Find the last time table was updated

sql-server-2008-r2
Ramesh Durai picture Ramesh Durai · Jul 5, 2013 · Viewed 219.7k times · Source

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?

Answer

marc_s picture marc_s · Jul 5, 2013

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.

Related questions

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated

I have many users on my web site (20000-60000 per day), which is a download site for mobile files. I have remote access to my server (windows server 2008-R2). I've received "Server is unavailable" errors before, but am now seeing …

Read More
Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)

I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build). Sometimes when I run my build I get this error: ALTER DATABASE failed because a lock could not be placed on …

Read More
Select SQL Server database size

how can i query my sql server to only get the size of database? I used this : use "MY_DB" exec sp_spaceused I got this : database_name database_size unallocated space My_DB 17899.13 MB 5309.39 MB It returns me several …

Read More