How to find unused tables in SQL Server

Question

Is there a way of finding out when the data was last entered into a table? I'm trying to find obsolete tables within my database and would like to know if there is a simple script(s) that I can run?

Answer 1

If this is important to your application(s) and/or company, and the tables were designed correctly, then each table should have a column called something like 'LastModifiedTime'. You can query this table to determine which tables are obsolete.

Answer 2

You could try check the results of querying the sys.dm_db_index_usage_stats Dynamic Management View like this:

SELECT *
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID() 
    AND [object_id] = OBJECT_ID('TableName')

This will return things like the last_user_seek, scan and update dates on the indexes on the table.

Howvever, beware as the stats for the dynamic management view are reset when the server is restarted. The longer the server has been running, the more confidence you can have if the records show no activity.

I personally would also be checking all the source code to check for references to the table in question, and searching all sprocs/UDFs for references too (you can use SQL Search from Red Gate to do this - it's free)