未知数量的tablename_XXXX类表跨表查询重复claimnumber方案

针对表数量未知且可能新增的场景，静态SQL没法处理动态表名的问题，动态SQL是最直接的解决办法，具体实现如下：

核心实现代码 #

直接执行这段SQL就能得到所有跨表重复的claimnumber及重复次数：

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

-- 拼接所有目标表的claimnumber查询
SELECT @sql += N'
SELECT claimnumber FROM ' + QUOTENAME(NAME) + N' UNION ALL'
FROM sys.tables
WHERE NAME LIKE 'tablename_%';

-- 移除最后多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10);

-- 包装成分组统计的逻辑
SET @sql = N'
WITH AllClaims AS (
    ' + @sql + N'
)
SELECT claimnumber, COUNT(*) AS duplicate_count
FROM AllClaims
GROUP BY claimnumber
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;';

-- 执行动态生成的SQL
EXEC sp_executesql @sql;

关键细节解释 #

• QUOTENAME(NAME)：给表名加上方括号，避免表名包含空格、特殊字符时出现语法错误
• 移除末尾UNION ALL：循环拼接会多出来最后一个UNION ALL，用LEFT截断即可（UNION ALL刚好是10个字符）
• sp_executesql：比直接用EXEC()更安全，支持参数化，后续扩展过滤条件也更方便

可选优化：排除空值 #

如果部分表的claimnumber可能为空，不想统计空值的重复，修改拼接语句即可：

SELECT @sql += N'
SELECT claimnumber FROM ' + QUOTENAME(NAME) + N' WHERE claimnumber IS NOT NULL UNION ALL'
FROM sys.tables
WHERE NAME LIKE 'tablename_%';

封装成存储过程（方便复用） #

如果需要定期执行这个查询，可以把逻辑封装成存储过程，以后直接调用就行：

CREATE PROCEDURE GetDuplicateClaimNumbers
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) = N'';

    SELECT @sql += N'
    SELECT claimnumber FROM ' + QUOTENAME(NAME) + N' WHERE claimnumber IS NOT NULL UNION ALL'
    FROM sys.tables
    WHERE NAME LIKE 'tablename_%';

    SET @sql = LEFT(@sql, LEN(@sql) - 10);

    SET @sql = N'
    WITH AllClaims AS (
        ' + @sql + N'
    )
    SELECT claimnumber, COUNT(*) AS duplicate_count
    FROM AllClaims
    GROUP BY claimnumber
    HAVING COUNT(*) > 1
    ORDER BY duplicate_count DESC;';

    EXEC sp_executesql @sql;
END

调用方式：EXEC GetDuplicateClaimNumbers;

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