Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 7 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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