Finding the last time a table was updated

Question

The query:

SELECT 
    name AS TableName, 
    create_date AS CreatedDate, 
    modify_date as ModifyDate 
FROM sys.tables 
order by ModifyDate;

...will tell me the last time a table was created and modified (from a DDL perspective). But I want to know the last time actual data was either inserted or removed from the table. Is it possible to get this in SQL Server?

Answer 1

You might be able to get an idea from

SELECT last_user_update
FROM   sys.dm_db_index_usage_stats us
       JOIN sys.tables t
         ON t.object_id = us.object_id
WHERE  database_id = db_id()
       AND t.object_id = object_id('dbo.YourTable') 

but the data there is not persisted across service restarts and might not be accurate for your requirements (e.g. running DELETE FROM T WHERE 1=0 will update the time even though no rows were actually deleted)