In MS SQL how do I find out what tables have not been queried or updated in a long time?

Question

I'm maintaining a database that is being shared by a few systems. Over the years a few of those systems have been deprecated and lots of the tables are actually orphaned. Is there a query I can run to find out which tables have not been used since a certain date?

Thanks.

Answer 1

The sys.objects table keeps track of the last time a table's structure was modified but the closest you are going to get for finding out the last time a table's data was updated or used is by querying sys.dm_db_index_usage_stats. This keeps track of index updates (including heap's, or tables without an index). The catch is, it only keeps track of this data since the last time you're server was restarted.

select db_name(database_id) dbname,
object_name(object_id,database_id) oname,
MAX(CASE WHEN last_user_update < last_system_update THEN last_system_update ELSE last_user_update END) as LastUpdated 
from sys.dm_db_index_usage_stats 
group by database_id,object_id
order by db_name(database_id),object_name(object_id,database_id)

This sample query was borrowed from the following link: http://www.sqlservercentral.com/Forums/Topic852747-146-1.aspx#bm852757