How Do I ... Get a List of Tables With Data Modifications in a Specific Timeframe?

There was a question on a discussion group today about how to get a list of tables that have had data modifications (updates, deletes, or inserts) in the last 24 hours. A few suggestions had been made before I joined the conversation, but they had all been discounted for various reasons. This is for SQL Server 2005, so CDC and Change Tracking are not possible. Furthermore, it is a SAP database with more than 75,000 tables and neither the database schema nor the application can be altered.

My Recommendation

My recommendation was to use the index usage DMV sys.dm_db_index_usage_stats. If there have been any updates to a table, it will show in this DMV for index ID’s 0 or 1. Index ID of 0 is a heap and index ID of 1 is the clustered index. Clustered indexes and heaps contains all data, so any modifications to the data in the table will be visible in this DMV.

My Reply:

You can check the sys.dm_db_index_usage_stats DMV to see when the last time the clustered index or heap was updated by a user query. Note that the second example requires that they have at least SQL 2005 SP2. Prior to SP2, the OBJECT_NAME() function did not accept a database ID as a parameter. If pre-SP2, they can only use the first option to run it in the context of a database. Also, the database it is running in has to be set to compatibility level 90 (SQL 2005). It won’t run successfully if it is in compatibility level 80 (SQL 2000).

Run in the context of the database you want to see data from:

Select OBJECT_NAME(object_id) As TableName

From sys.dm_db_index_usage_stats

Where user_updates > 0

And last_user_update > DATEADD(hour, -24, getdate())

And index_id In (0, 1)

And database_id = DB_ID()

Or run from any database to see the data for all databases:

Select OBJECT_NAME(object_id, database_id) As TableName

From sys.dm_db_index_usage_stats

Where user_updates > 0

And last_user_update > DATEADD(hour, -24, getdate())

And index_id In (0, 1)