SQL Server - obtaining table's last write date

Factual Questions
1

Is there any simple way (e.g. a query) to obtain the date-time at which a database table’s data was last changed? I need something analogous to a file’s LastWriteTime.

I note that sys.objects offers ‘modify_date’ but this refers to modification of the schema, whereas I need to know about changes to the data.

2

I can’t think of one, unless you purposely add code to do so. We have some tables here that have accompanying archive tables. The main tables have triggers that write the last entry to the archive, along with a GetDate().

3

I’d agree. SQL Server doesn’t track data modification dates, you have to insert that info into a table to keep it. There’s several ways to do that.

4

Thirded. Most of our (well designed) tables at work have a “LastUpdatedOn” datetime field for each record so i just get the max of that field.

5

Rats.

(And thanks for the responses.)

6

If you’re using SQL Server 2005 or later, there’s a partial solution to your problem, not the recommended approach, but a quick-and-dirty one (with several caveats) that might solve problems temporarily.

The well-designed approach would be the one noted above, with a trigger or a LastUpdatedOn datetime field, but in the meantime try this:



SELECT 
    last_user_update

FROM
    sys.dm_db_index_usage_stats

WHERE
    [database_id] = db_id('DB_Name')

AND
    [object_id] = object_id('Table_Name')

The caveats are that this data is transitory and is reset every time SQL Server is restarted, or when the database is detached.