Do we have sql logic to remove unused tables on the basis of given duration and table contain test or backup prefix.
how do you define unused duration?
SELECT DB_NAME(ius.[database_id]) AS [Database], OBJECT_NAME(ius.[object_id]) AS [TableName], MAX(ius.[last_user_lookup]) AS [last_user_lookup], MAX(ius.[last_user_scan]) AS [last_user_scan], MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius WHERE ius.[database_id] = DB_ID() AND ius.[object_id] in (select object_id from sys.tables ) GROUP BY ius.[database_id], ius.[object_id];
Unused duration means we mention one date and after that date if any table is not used, those tables will come under unwanted table list
and what would you do with them, mark them as unwanted table in a audit table (hopefully audit table itself does not come under that and be removed ) Or do you actually want to drop/delete those tables?
And what is the purpose of this process?
The DMV is no good is this case.The server is restarted couple of times after a particular table was last accessed, is there a way to still check the last user access details?
Removing unwanted tables from production server purpose is very simple. User are creating those tables and forget to drop them. so we planed to drop all those tables those are not accessed by anyone from last one year.
So why are these tables created in the first place? IS this a one time manual thing or automated? proceed with caution. Why is user creating table in production database which user?
-- Create CTE for the unused tables, which are the tables from the sys.all_objects and -- not in the sys.dm_db_index_usage_stats table ; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate ) AS ( SELECT DBTable.name AS TableName ,PS.row_count AS TotalRowCount ,DBTable.create_date AS CreatedDate ,DBTable.modify_date AS LastModifiedDate FROM sys.all_objects DBTable JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name WHERE DBTable.type ='U' AND NOT EXISTS (SELECT OBJECT_ID FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = DBTable.object_id ) ) -- Select data from the CTE SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate FROM UnUsedTables
Good questions but no idea why were those tables were created. DMV is not good idea to identify unwanted tables as when ever server get restart those values get changed.
Any other solution to find out unused tables. e.g want to drop all those table who does not access from last one week(example for time duration)
Extremely dangerous. How would you know it is not an actual working table?
Suppose we have created 10 tables in our database before one month, out of 10 tables we are using only two table so we have to identify those table names are not using from last 7 days and need to drop them.