18

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?

24

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)

  • Is there any way I can run this for all tables in a database at a time,instead of single table ? thanks – SQLBoy Apr 4 '17 at 6:33
  • 3
    @SQLBoy 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() – Martin Smith Apr 4 '17 at 6:56
  • 1
    The last_user_update date is also updated afeter unique index violation exception. Is there any way around this? – Алена Шлыкова Feb 27 '19 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.