如何获取CDC表中存在修改值的列名？SQL脚本执行报错

问题场景 #

需要从CDC表cdc.dbo_OINV_CT中找出存在修改值的列名，原脚本执行时触发报错：

Msg 191, Level 15, State 1, Line 1712
Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.

原脚本通过游标循环拼接大量EXISTS子句，当表列数较多时，会超出SQL Server的嵌套层级限制。

解决方案 #

方案1：通过UNION ALL拆分查询（避免嵌套） #

该方案将每列的检查逻辑拆分为独立查询，通过UNION ALL合并结果，彻底避免嵌套过深问题：

DECLARE @SQLQuery NVARCHAR(MAX) = N'';

-- 为每个列生成独立的检查查询
SELECT @SQLQuery += N'
UNION ALL
SELECT ''' + COLUMN_NAME + ''' AS ChangedColumn
WHERE EXISTS (
    SELECT 1
    FROM cdc.dbo_OINV_CT t1
    JOIN cdc.dbo_OINV_CT t2 
        ON t1.__$start_lsn = t2.__$start_lsn 
        AND t1.__$seqval = t2.__$seqval
    WHERE t1.__$operation = 3 -- 更新前记录
      AND t2.__$operation = 4 -- 更新后记录
      AND t1.' + QUOTENAME(COLUMN_NAME) + ' <> t2.' + QUOTENAME(COLUMN_NAME) + '
)'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'dbo_OINV_CT' 
  AND TABLE_SCHEMA = 'cdc'
  AND COLUMN_NAME NOT IN ('__$start_lsn', '__$seqval', '__$operation', '__$update_mask'); -- 排除CDC系统列

-- 清理并执行动态SQL
IF @SQLQuery <> N''
BEGIN
    -- 移除开头多余的UNION ALL，包装成子查询去重
    SET @SQLQuery = STUFF(@SQLQuery, 1, 11, N'SELECT DISTINCT ChangedColumn FROM (');
    SET @SQLQuery += N') AS ChangedColumns';
    EXEC sp_executesql @SQLQuery;
END
ELSE
BEGIN
    PRINT 'No columns found in cdc.dbo_OINV_CT.';
END

方案2：利用CDC内置的__$update_mask字段（高效推荐） #

CDC表的__$update_mask字段是位图标识，每一位对应表中一列的修改状态，无需关联更新前后记录，效率更高：

SELECT 
    c.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.COLUMNS c
JOIN 
    sys.columns sc 
        ON c.COLUMN_NAME = sc.name 
        AND OBJECT_ID('cdc.dbo_OINV_CT') = sc.object_id
WHERE 
    c.TABLE_NAME = 'dbo_OINV_CT' 
    AND c.TABLE_SCHEMA = 'cdc'
    AND EXISTS (
        SELECT 1
        FROM cdc.dbo_OINV_CT t
        WHERE t.__$operation IN (3,4) -- 仅筛选更新操作的记录
          AND (t.__$update_mask & POWER(2, sc.column_id - 1)) > 0 -- 检查列对应的位是否被置1
    )
    AND c.COLUMN_NAME NOT IN ('__$start_lsn', '__$seqval', '__$operation', '__$update_mask'); -- 排除系统列

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