Is there meta data I can read from SQL Server to know the last changed row/table?

Question

We have a database with hundreds of tables.

Is there some kind of meta data source in SQL Server that I can programatically query to get the name of the last changed table and row?

Or do we need to implement this ourselves with fields in each table called LastChangedDateTime, etc.?

Answer 1

The [sys].[tables] view will tell you when the table was created and last modified (in terms of schema, not insert, updates or deletes). To my knowledge there is no built-in information about last modified for each record in the database (it would take up a lot of space anyway, so it's probably nice not to have it). So you should add a last modified field yourself, and maybe have it updated automatically by a trigger.

Answer 2

In terms of finding out when a table last had a modification, there is a sneaky way that can work to access this information, but it will not tell you which row was altered, just when. SQL Server maintains index usage statistics, and records the last seek / scan / lookup and update on an index. It also splits this by user / system.

Filtering that to just the user tables, any insert / update / deletion will cause an update to occur on the index, and the DMV will update with this new information.

select o.name, 
max(u.last_user_seek) as LastSeek, 
max(u.last_user_scan) as LastScan, 
max(u.last_user_lookup) as LastLookup,
max(u.last_user_update) as LastUpdate 
from sys.dm_db_index_usage_stats u
inner join sys.objects o on  o.object_id = u.object_id
where o.type = 'U' and o.type_desc = 'USER_TABLE'
group by o.name

It is not ideal however, a heap has no index for a start - and I have never considered using it for production code as a tracking mechanism, only as a forensic tool to check obvious alterations.

If you want proper row level alteration tracking you will either have to build that in, or look at the SQL 2008 specific Change Data Capture feature.

Answer 3

Depending on the recovery model you might be able to get this from the transaction log using fn_dblog http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-6-Using-fn_dblog-to-tell-if-a-transaction-is-contained-in-a-backup.aspx