SQL: How to get the last datetime updated of a table

For Database administrators, keeping track of the last date time when a table got updated is vital for a database audit. If you have an entry date field on your table that contains default value of GETDATE() then you can make use of it by using ORDER BY entrydate query. But of course we can't expect all tables to have such field. No worry because SQL server has Dynamic Management View (DMV), admin can query on sys.dm_db_index_usage_stats and retrieve the datetime when the table was last updated.


To get the last update from sys.dm_db_index_usage_stats, user can retrieve it from last_user_update field as shown in the SQL script below:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'TestDatabase')
AND OBJECT_ID=OBJECT_ID('tblTest')

If you want to get the last update on all tables in a database you can use the sys.tables.

SELECT
b.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
FROM
sys.dm_db_index_usage_stats a JOIN
sys.tables b ON (b.object_id = a.object_id)
WHERE database_id = db_id()

Please take note that if the SQL Server is restarted all information from DMV will be reset.

For more SQL Coding Tips and Tricks, subscribe now