SQL 如何列出上周插入删除或更新数据的表

How to list tables where data was inserted deleted or updated in last week

提问by Laxmi Lal Menaria

I have sql server 2008 databases, I would like to know which tables was updated last week i.e. tables which has new rows, updated existing rows or which rows are deleted.

我有 sql server 2008 数据库，我想知道上周更新了哪些表，即有新行、更新现有行或删除哪些行的表。

Is there any way to do this for existing database.

有没有办法对现有数据库执行此操作。

回答by Devart

Try this one -

试试这个——

SELECT 
      [db_name] = d.name
    , [table_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , s.last_user_update
FROM sys.dm_db_index_usage_stats s
JOIN sys.databases d ON s.database_id = d.database_id
JOIN sys.objects o ON s.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
    AND s.last_user_update IS NOT NULL
    AND s.last_user_update BETWEEN DATEADD(wk, -1, GETDATE()) AND GETDATE()

回答by Francesco De Lisi

Try with Change Data Capture. It's a good way to keep track of your change on the DB. You have to enable the feature on one or more DBs, then on one or more table (it's a Table feature, so you will do it for every table you need).

尝试使用变更数据捕获。这是跟踪数据库更改的好方法。您必须在一个或多个 DB 上启用该功能，然后在一个或多个表上启用该功能（这是一个表功能，因此您将为您需要的每个表执行此操作）。

---

---

Enable CDC on database.

在数据库上启用 CDC。

Let's assume we want to enable CDC for AdventureWorks database. We must run the following SP to be sure this feature will work:

假设我们要为 AdventureWorks 数据库启用 CDC。我们必须运行以下 SP 以确保此功能正常工作：

USE AdventureWorks 
GO 
EXEC sys.sp_cdc_enable_db 
GO

As result, we'll find a new schema called cdcand several tables automatically added:

结果，我们会找到一个名为cdc的新模式，并自动添加了几个表：

• cdc.captured_columns– This table returns result for list of captured column.
• cdc.change_tables– This table returns list of all the tables which are enabled for capture.
• cdc.ddl_history– This table contains history of all the DDL changes since capture data enabled.
• cdc.index_columns– This table contains indexes associated with change table.
• cdc.lsn_time_mapping– This table maps LSN number and time.

• cdc.captured_columns– 此表返回捕获列列表的结果。
• cdc.change_tables– 此表返回所有启用捕获的表的列表。
• cdc.ddl_history– 此表包含自启用捕获数据以来所有 DDL 更改的历史记录。
• cdc.index_columns– 该表包含与变更表相关的索引。
• cdc.lsn_time_mapping– 此表映射 LSN 编号和时间。

---

---

Enable CDC on table.

在表上启用 CDC。

After having enabled CDC on desired DB(s) it's time to check if there are tables with this feature on:

在所需的数据库上启用 CDC 后，是时候检查是否有具有此功能的表：

USE AdventureWorks 
GO 
SELECT [name], is_tracked_by_cdc  
FROM sys.tables 
GO

If not, we can enable the changes capture for HumanResources.Shift table with the following procedure:

如果没有，我们可以使用以下过程为 HumanResources.Shift 表启用更改捕获：

USE AdventureWorks 
GO 
EXEC sys.sp_cdc_enable_table 
@source_schema = N'HumanResources', 
@source_name   = N'Shift', 
@role_name     = NULL 
GO

Be sure you SQL Agentis up and running because it will create a job (cdc.AdventureWorks_capture probably) to catch the modifications. If all procedures are correctly executed we'll find a new table called cdc.HumanResources_Shift_CT, among the system tables, containing all the HumanResources.Shift changes.

请确保您的SQL 代理已启动并正在运行，因为它会创建一个作业（可能是 cdc.AdventureWorks_capture）来捕获修改。如果所有过程都正确执行，我们将在系统表中找到一个名为 cdc.HumanResources_Shift_CT 的新表，其中包含所有 HumanResources.Shift 更改。

Note: be careful with @role_name parameter, it specifies database infos access.

注意：注意@role_name 参数，它指定数据库信息访问。

回答by Dragan Radivojevic

There is no way to find out this info by default unless you already have some auditing system installed.

除非您已经安装了一些审计系统，否则默认情况下无法找到此信息。

Only option, assuming your database is in full recovery mode, is to read transaction log and try get info from there.

假设您的数据库处于完全恢复模式，唯一的选择是读取事务日志并尝试从那里获取信息。

You can try reading transaction log using sql server functions DBCC LOG and fn_dblog or using third party tools such as ApexSQL Log.

您可以尝试使用 SQL Server 函数 DBCC LOG 和 fn_dblog 或使用第三方工具（如ApexSQL Log）读取事务日志。

回答by Raj

As commented by others, Change Data Capture and Change Tracking would be the ideal solutions if you have implemented them. If you have not, then here is a quick way, which will give you info partially:

正如其他人评论的那样，如果您实施了变更数据捕获和变更跟踪，它们将是理想的解决方案。如果你还没有，那么这里有一个快速的方法，它会给你部分信息：

SELECT OBJECT_NAME(OBJECT_ID) AS Object, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( <YourDBName>)
AND OBJECT_ID=OBJECT_ID(<YourTableName>)

This will tell you what tables were updated when, but may not really serve your purpose, if you are faced with a whodunnit scenario. Also, it will only give you the latest updated timestamp, meaning, if someone updated last week and some one else updated yesterday, you will not see the last week update details.

这将告诉您哪些表在何时更新，但如果您面临未知情况，则可能无法真正满足您的目的。此外，它只会为您提供最新更新的时间戳，这意味着，如果有人上周更新而其他人昨天更新，您将看不到上周更新的详细信息。

Raj

拉吉

回答by Rohan

Try this.It will provide last updated date for each table in database.

试试这个。它将为数据库中的每个表提供最后更新的日期。

USE database_name

GO


SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats

GO

For more reference click here

更多参考请点击这里