Script to find when a table was last modified

I have had a question from one of my collegues, how to find when was a table last modified.

Here is what I suggest to use to find when was tables schema changed and when was the last user update/delete on the table.

Script to find the last schema change date of a table, you will need to supply table name in the where clause. Run this query in the target database.

SELECT  name AS TableName ,
modify_date AS Last_schema_Change
FROM    sys.objects
WHERE   name = ‘<table>’

Script to find when the table data was last changed, here you will need to specify the database name and the table number.

SELECT  OBJECT_NAME(OBJECT_ID) AS TableName ,
last_user_update ,
user_updates ,
index_id
FROM    sys.dm_db_index_usage_stats
WHERE   database_id = DB_ID(‘<databasename>’)
AND OBJECT_ID = OBJECT_ID(‘<tablename>’)

This entry was posted in SQL Server, T-SQL Scripts, Tricks and tagged , . Bookmark the permalink.

Leave a Reply