Finding the last time a table was updated

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?

You might be able to get an idea from

SELECT last_user_update
FROM   sys.dm_db_index_usage_stats
WHERE  database_id = db_id()
       AND 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)

Is there any way I can run this for all tables in a database at a time, instead of single table?

SELECT last_user_update, t.name
FROM   sys.dm_db_index_usage_stats us
       JOIN sys.tables t
         ON t.object_id = us.object_id
WHERE  database_id = db_id()

StackExchange Database Administrators Q#12749, answer score: 40