Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find the last time table was updated

I want to retrieve the last time table was updated(insert,delete,update).

I tried this query.

SELECT last_user_update FROM sys.dm_db_index_usage_stats WHERE object_id=object_id('T') 

but the data there is not persisted across service restarts.

I want to preserve the stats even if the service restarts. How can I achieve it?

like image 772
Ramesh Durai Avatar asked Jul 05 '13 12:07

Ramesh Durai


1 Answers

If you're talking about last time the table was updated in terms of its structured has changed (new column added, column changed etc.) - use this query:

SELECT name, [modify_date] FROM sys.tables 

If you're talking about DML operations (insert, update, delete), then you either need to persist what that DMV gives you on a regular basis, or you need to create triggers on all tables to record that "last modified" date - or check out features like Change Data Capture in SQL Server 2008 and newer.

like image 138
marc_s Avatar answered Sep 23 '22 22:09

marc_s