Find the last time table was updated

Question

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

User · Accepted Answer

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

User · Answer

SELECT so name so modify date  FROM sys objects as so  INNER JOIN INFORMATION SCHEMA TABLES as ist  ON ist TABLE NAME so name where ist TABLE TYPE  BASE TABLE  AND   TABLE CATALOG  DbName  order by so modify date desc    this is help to get table modify with table name

User · Answer

To persist audit data regarding data modifications  you will need to implement a DML Trigger on each table that you are interested in  You will need to create an Audit table  and add code to your triggers to write to this table   For more details on how to implement DML triggers  refer to this MDSN article http   msdn microsoft com en-us library ms191524 28v sql 105 29 aspx

User · Answer

Find last time of update on a table   SELECT tbl name  ius last user update  ius user updates  ius last user seek  ius last user scan  ius last user lookup  ius user seeks  ius user scans  ius user lookups FROM sys dm db index usage stats ius INNER JOIN sys tables tbl ON  tbl OBJECT ID   ius OBJECT ID  WHERE ius database id   DB ID        http   www sqlserver-dba com 2012 10 sql-server-find-last-time-of-update-on-a-table html

User · Answer

Why not just run this  No need for special permissions  SELECT     name       object id       create date       modify date FROM     sys tables  WHERE      name like   yourTablePattern   ORDER BY     modify date

User · Answer

If you want to see data updates you could use this technique with required permissions   SELECT OBJECT NAME OBJECT ID  AS DatabaseName  last user update   FROM sys dm db index usage stats WHERE database id   DB ID   DATABASE   AND OBJECT ID OBJECT ID  TABLE

[sql-server-2008-r2] Find the last time table was updated

Examples related to sql-server-2008-r2