Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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)

Comments

Posted by Kendra Little on 18 June 2010

This is a great post! That DMV really is  useful for more than just index tuning. I wasn't aware about that change in sql 2k5 sp2, that's an especially great detail.

Posted by Robert L Davis on 18 June 2010

Yeah, as a matter of fact, using that function helped me identify a SQL Server in our environment that was still SP1 about 6 months ago.

Posted by Jason Brimhall on 19 June 2010

I knew those columns were available in that view, but had not considered the solution for the question.

Excellent recommendation and it makes a lot of sense.

Thanks.

Posted by Robert L Davis on 19 June 2010

Thanks Jason and Kendra!!

Posted by Dukagjin Maloku on 20 June 2010

This is the way how SQL Master talking! - no more comment, really very short, simple and professional explanation!

Thank you!

Posted by Robert L Davis on 20 June 2010

Thanks Dukagjin!!

Posted by Steve Jones on 21 June 2010

Very cool trick. Just caught up with this entry, but I can see this as being handy for finding out if *something* has changed.

Posted by Robert L Davis on 21 June 2010

Better late than never. :)

Thanks!!

Leave a Comment

Please register or log in to leave a comment.