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

Question

I am trying to clear a database and want to determine which tables are no longer needed, so I can delete them.

( SQL Server 2000 )

Thanks in advance!

Answer 1

Yes - such a request should provide you with the necessary information:

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 is no built-in ability to retrieve this information in SQL 2000

Answer 2

For SQL Server 2005+

sys.dm_db_index_usage_stats tells you when the last time a plan was executed that referred to a specific table (it is not necessary that the table itself is available when this plan is executed ).

This metadata is not stored in all service restarts, so you will need to check this after your server has been running for some time.

For SQL Server 2000

No, It is Immpossible. I believe. You could say something about when they were last modified by looking at updated statistics and sysindexes.rowmodctr , but I don’t think the last time they were selected was saved anywhere.

Answer 3

In general, I would be wary of judgments based on date / time. Some applications may have features that are rarely used. For example, a table containing year-end financial statements can only be updated once a year. Or auditors may appear someday and request financial results for previous years, and your company will have big problems if it cannot provide this information.

If you have access to the code, I recommend checking queries to access each table.

If you do not, you can get some information from the index usage statistics, as recommended by other answers. But be careful!

[edit] I see that you have SQL Server 2000. The usage statistics are not in this version, and I don’t think there is any equivalent.

If the table has datetime columns, you can see when these dates are, but that will not tell you when they were last.

If the table has timestamp columns, you can compare the last timestamp with the last database timestamp: @@dbts and see if the values ​​are Very different. But this is an invalid assumption at best.