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?

Cardiograph

Just about to ask this myself – Sundries

Answer 1

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.

Accomplished

The solution will work only in the Enterprise, Developer, and Enterprise Evaluation editions. – Cardiograph

@RameshDurai: which solution are you talking about? The sys.tables is present in ALL editions of SQL Server. But the CDC (Change Data Capture) might be an Enterprise-only edition feature, that's true – Accomplished

Change Data Capture. Because I need to get the updated time for DML operations. – Cardiograph

This isn't table data update time. This gives us table sutructure modification time. If you want to see data edit time; i've added an answer. – Entangle

For non-default schemaSELECT s.name [schema], t.name [table], t.modify_date FROM sys.tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id – Pyralid

I modified query SELECT name, [modify_date] FROM sys.tables ORDER BY modify_date DESC – Reg

Answer 2

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

Jueta

This solution does not work if the service is restarted. – Cardiograph

This is specifically what OP is trying to avoid. – Screamer

The last_user_update date is also updated after unique index violation exception. Is there any way around this? – Dossier

I did it. But Showed me 'last_user_update' Column is Null – Raffin

Answer 3

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

Evaleen

This is exactly what I was looking for; I simply need to know how recently something (anything!) in the database was updated, to give me a rough idea whether the database has been in use recently or has been idle for a while (since the last server restart). – Paulettepauley

Answer 4

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

Seignior

Answer 5

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

Pegboard

This query is just for when a table schema was last modified – Ideography

Answer 6

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

It

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Gastric

Answer 7

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

Halima

This isn't table data update time. This gives us table structure modification time. – Cardiograph