Unwanted table removal on the basis of given date

SQL Server Administration
1

Do we have sql logic to remove unused tables on the basis of given duration and table contain test or backup prefix.

2

how do you define unused duration?

try this from https://blog.sqlauthority.com/2013/03/21/sql-server-identify-last-user-access-of-table-using-t-sql-script/

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];
3

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

4

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 :smiley:) Or do you actually want to drop/delete those tables?
And what is the purpose of this process?

5

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?

6

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.

7

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?

maybe this?

-- 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
8

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)

9

Extremely dangerous. How would you know it is not an actual working table?

10

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.