Is there any metadata that I can read from SQL Server to find out the last changed row / table?

We have a database with hundreds of tables.

Is there any meta strong> data source in SQL Server for which I can program the query to get the last changed table name and row?

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

+3
source share
3 answers

In terms of clarification, when the last table had a modification, there is a hidden way that can work to access this information, but it will not tell you which row was changed only when. SQL Server maintains index usage statistics and records the last index search / scan / search and update. It also breaks it down into a user / system.

Filtering is only for user tables, any insert / update / delete will cause an update to appear in the index, and the DMV will update 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 

However, it is not perfect, the heap does not have an index to start with - and I never considered using it for production code as a tracking mechanism, only as a forensic tool to check for obvious changes.

If you need to properly track changes at the row level, you will have to either create one or look at the specific SQL 2008 data modification function.

+3
source

The [sys].[tables] view will indicate when the table was created and modified (in terms of schema, not insert, update or delete). As far as I know, there is no built-in information about the latest changes for each record in the database (this will take up a lot of space, so it's probably nice not to have it). Therefore, you should add the last changed field yourself and, possibly, update it automatically using a trigger.

+1
source

Depending on the recovery model, you can 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

0
source

Source: https://habr.com/ru/post/1304986/


All Articles