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?

1

7 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.

6

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')

4

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()

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

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

1

SELECT UPDATE_TIME
FROM information_schema.tables WHERE TABLE_SCHEMA = 'your_dbname' AND TABLE_NAME = 'your_tablename'

1

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

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.