Is there a function/feature in SQL Server to determine if a table has any (recent) activity?

Question

I'm trying to clean up a database and want to determine which tables are no longer needed so I can drop them.

(SQL Server 2000)

Thanks in advance!

Answer 1

For SQL Server 2005+

sys.dm_db_index_usage_stats tells you when a plan was last executed that referenced a particular table (not necessarily that the table itself was accessed in the execution of that plan).

This metadata is not persisted across service restarts however so you'd need to check it after your server has been up for a while.

For SQL Server 2000

No this isn't possible I believe. You might be able to infer something about when they were last modified by looking at statistics updated times and sysindexes.rowmodctr but I don't think the last time they were selected from was persisted anywhere.

Answer 2

Yes - a query such as this should give you the information you need:

SELECT 
        last_user_seek,
        last_user_scan
    FROM
        sys.dm_db_index_usage_stats
    WHERE
        database_id = DB_ID() and object_id = object_id('tablename')

Update for SQL 2000: No - I believe there's no built in way of getting this information in SQL 2000