SQL Server禁用CDC时，如何定位接收增删改操作的表？

你的问题出在sys.tables的modify_date字段只记录表结构的修改时间（比如添加列、修改约束、更改索引这类操作），数据的插入/更新/删除不会更新这个值，所以你手动改数据后查不到结果很正常。

下面给你几个不需要启用CDC的替代方案：

1. 使用sys.dm_db_index_usage_stats动态管理视图 #

这个视图会记录SQL Server启动以来，表上的索引读写操作统计，能间接判断哪些表有数据变更。注意：SQL服务重启后，这个视图的数据会被清空。

查询语句：

SELECT 
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    MAX(last_user_update) AS last_data_modified_time
FROM 
    sys.dm_db_index_usage_stats u
JOIN 
    sys.objects o ON u.object_id = o.object_id
WHERE 
    o.type = 'U' -- 只查用户表
    AND last_user_update IS NOT NULL
GROUP BY 
    SCHEMA_NAME(o.schema_id), o.name
ORDER BY 
    last_data_modified_time DESC;

• last_user_update：记录最后一次用户发起的插入/更新/删除操作时间
• 这个方法不需要提前配置，但只能追踪服务启动后的操作，而且如果表没有索引（比如堆表），可能统计不到。

2. 用触发器记录变更日志 #

如果允许在数据库中创建对象，可以给目标表建触发器，把每次数据变更记录到一个专门的日志表中，这种方式最精准。

步骤：

1. 先创建日志表：

CREATE TABLE DataChangeLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    ChangeType NVARCHAR(10), -- INSERT/UPDATE/DELETE
    ChangeTime DATETIME DEFAULT GETDATE(),
    ChangedBy SYSNAME DEFAULT SUSER_SNAME()
);

2. 给需要监控的表创建触发器（以某表为例）：

CREATE TRIGGER trg_TableName_ChangeLog
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ChangeType NVARCHAR(10);
    
    IF EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
        SET @ChangeType = 'UPDATE';
    ELSE IF EXISTS(SELECT * FROM INSERTED)
        SET @ChangeType = 'INSERT';
    ELSE
        SET @ChangeType = 'DELETE';
    
    INSERT INTO DataChangeLog (SchemaName, TableName, ChangeType)
    VALUES (SCHEMA_NAME(), OBJECT_NAME(@@PROCID), @ChangeType);
END;

• 优点：能精准记录每一次变更的类型、时间和操作人
• 缺点：需要逐个给表加触发器，对高频操作的表有轻微性能影响

3. 读取事务日志（fn_dblog） #

SQL Server的事务日志会记录所有数据变更，你可以用fn_dblog函数直接读取，但要注意：如果数据库是简单恢复模式，日志会自动截断，只能查到最近的操作；而且需要VIEW SERVER STATE或VIEW DATABASE STATE权限。

查询语句：

SELECT 
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name,
    MAX(l.[Begin Time]) AS last_change_time,
    CASE 
        WHEN l.Operation IN ('LOP_INSERT_ROWS') THEN 'INSERT'
        WHEN l.Operation IN ('LOP_DELETE_ROWS') THEN 'DELETE'
        WHEN l.Operation IN ('LOP_MODIFY_ROW', 'LOP_MODIFY_COLUMNS') THEN 'UPDATE'
        ELSE 'UNKNOWN'
    END AS change_type
FROM 
    sys.fn_dblog(NULL, NULL) l
JOIN 
    sys.objects o ON l.AllocUnitId = OBJECT_ID(o.name)
WHERE 
    o.type = 'U'
    AND l.Operation IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW', 'LOP_MODIFY_COLUMNS')
GROUP BY 
    SCHEMA_NAME(o.schema_id), o.name, change_type
ORDER BY 
    last_change_time DESC;

• 优点：不需要提前配置，能查到历史变更（只要日志没被截断）
• 缺点：日志内容复杂，查询结果可能有冗余，权限要求高

你可以根据自己的权限、数据库恢复模式和需求选择合适的方法。

内容的提问来源于stack exchange，提问作者Arnold Zahrneinder