如何使用SQL Server变更跟踪获取所有变更表及对应变更行ID

你不需要额外编写多表JOIN逻辑，只需要调整原有循环的处理逻辑：将原本仅统计变更行数的逻辑，替换为直接读取每个表的CHANGETABLE结果，统一插入到结构化的结果表中即可。

调整后完整脚本 #

set nocount on;

-- 自定义变更追踪起始版本，可替换为你之前留存的版本号
--declare @prevTrackingVersion int = INSERT_YOUR_PREV_VERSION_HERE
-- 默认取当前版本的上一个版本作为变更查询起点
declare @prevTrackingVersion int = CHANGE_TRACKING_CURRENT_VERSION() - 1

-- 拉取所有开启了变更追踪的表清单
declare @trackedTables as table (name nvarchar(1000));
insert into @trackedTables (name)
select sys.tables.name 
from sys.change_tracking_tables 
join sys.tables ON tables.object_id = change_tracking_tables.object_id

-- 定义最终结果表，存储变更所属表名、变更行ID
declare @changedResults as table (
    tableName nvarchar(1000),
    rowId bigint
);

declare @tableName nvarchar(1000)
declare @sql nvarchar(max)
while exists(select top 1 * from @trackedTables)
begin
    -- 按表名顺序取待处理的表
    set @tableName = (select top 1 name from @trackedTables order by name asc);

    -- 动态拼接查询语句，拉取当前表所有变更行ID写入结果表
    -- 注意：如果你的表主键列名不是ID，请将下方CT.ID替换为实际主键列名
    set @sql = N'
        insert into @changedResults (tableName, rowId)
        select @tableNamePara, CT.ID
        from CHANGETABLE(CHANGES ' + QUOTENAME(@tableName) + ', @prevVer) as CT
    '

    exec sp_executesql 
        @sql, 
        N'@changedResults table (tableName nvarchar(1000), rowId bigint) output, @prevVer int, @tableNamePara nvarchar(1000)',
        @changedResults output,
        @prevVer = @prevTrackingVersion,
        @tableNamePara = @tableName

    -- 处理完成后从待处理清单移除当前表
    delete from @trackedTables where name = @tableName;
end

-- 输出最终结果
select * from @changedResults;

使用说明 #

• 脚本默认所有开启变更追踪的表主键列名为ID，如果实际业务表主键列名不是ID，请修改动态SQL中CT.ID部分为对应主键列名；如果使用复合主键，需要调整结果表结构，增加对应主键列的存储字段。
• 拼接表名时使用QUOTENAME()做标识符包裹，可避免表名包含特殊字符、SQL关键字时出现语法错误，比直接字符串拼接更安全。
• 如果需要区分变更是新增/更新/删除，只需要在@changedResults表中新增changeType nchar(1)字段，动态SQL中同步查询CT.SYS_CHANGE_OPERATION字段写入即可，字段值I代表新增、U代表更新、D代表删除。

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