5

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.

9

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.