是否可以从 SQL Server 读取元数据以了解最后更改的行/表? [英] Is there meta data I can read from SQL Server to know the last changed row/table?

问题描述

我们有一个包含数百表的数据库.

We have a database with hundreds of tables.

SQL Server 中是否有某种元数据源，我可以以编程方式查询以获取上次更改表的名称和排?

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?

或者我们是否需要我们自己用每个表中名为LastChangedDateTime的字段来实现这个?

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

推荐答案

在找出表上次修改的时间方面，有一种偷偷摸摸的方法可以访问此信息，但它不会告诉您是哪个行被改变了，就在什么时候.SQL Server 维护索引使用统计信息，并记录对索引的最后一次搜索/扫描/查找和更新.它还按用户/系统进行拆分.

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.

仅过滤用户表，任何插入/更新/删除都会导致对索引进行更新，并且 DMV 将使用此新信息进行更新.

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.

如果您想要适当的行级更改跟踪，您要么必须将其内置，要么查看 SQL 2008 特定的变更数据捕获功能.

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.

这篇关于是否可以从 SQL Server 读取元数据以了解最后更改的行/表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！