You need to enable JavaScript to run this app. 如何在SQL Server各表总记录数统计中同步获取每个表的去重记录数
优惠活动
大模型
产品
解决方案
定价
更多

如何在SQL Server各表总记录数统计中同步获取每个表的去重记录数

实现思路

系统目录视图仅能存储表总记录数等近似元数据,整行去重的记录数没有对应元数据可直接读取,必须对每个表实际执行统计计算。之前尝试WHILE+CTE实现失败,基本是两个常见问题:

  • SQL Server 不支持直接写COUNT(DISTINCT *),必须显式列出表的所有列
  • 跨schema、带特殊字符的表名/列名没有用QUOTENAME做转义,导致拼接的SQL执行报错
    如果表本身带主键/非空唯一索引,整行去重的记录数和表总记录数完全相等,无需额外扫描计算,可大幅降低大表的统计开销。
完整实现代码
DECLARE @DbName SYSNAME = N'MyDb';
-- 临时表存储最终统计结果
CREATE TABLE #FinalResult (
    TableName NVARCHAR(500),
    TotalRowCount BIGINT,
    DistinctRowCount BIGINT
);
-- 第一步:沿用原有逻辑写入所有表的总记录数
INSERT INTO #FinalResult (TableName, TotalRowCount)
SELECT 
    QUOTENAME(@DbName) + '.' + QUOTENAME(SCHEMA_NAME([sOBJ].schema_id)) + '.' + QUOTENAME([sOBJ].[name]) AS TableName,
    SUM([sPTN].[Rows]) AS TotalRowCount
FROM [MyDB].[sys].[objects] AS [sOBJ]
INNER JOIN [sys].[partitions] AS [sPTN]
    ON [sOBJ].object_id = [sPTN].object_id
WHERE  
    [sOBJ].[type] = 'U'
    AND [sOBJ].[is_ms_shipped] = 0x0
    AND [index_id] < 2 -- 0:Heap, 1:Clustered
GROUP BY [sOBJ].schema_id, [sOBJ].[name];
-- 第二步:动态拼接统计语句,逐表计算去重记录数
DECLARE @DynamicSQL NVARCHAR(MAX) = N'';
SELECT 
    @DynamicSQL = @DynamicSQL + N'
UPDATE #FinalResult 
SET DistinctRowCount = ' + 
    -- 有主键/唯一非空索引的表直接取总记录数,无需扫表
    CASE WHEN EXISTS (
        SELECT 1 
        FROM [MyDb].sys.indexes si 
        WHERE si.object_id = so.object_id 
            AND si.is_unique = 1
            AND (si.is_primary_key = 1 OR si.is_unique_constraint = 1)
    ) 
    THEN CAST(TotalRowCount AS NVARCHAR(100))
    -- 无唯一索引的表,拼接全列DISTINCT统计语句
    ELSE N'(SELECT COUNT(DISTINCT ' + 
        STUFF((
            SELECT ',' + QUOTENAME(sc.name)
            FROM [MyDb].sys.columns sc
            WHERE sc.object_id = so.object_id
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 
    + N') FROM ' + QUOTENAME(@DbName) + '.' + QUOTENAME(SCHEMA_NAME(so.schema_id)) + '.' + QUOTENAME(so.name) + N')'
    END + N'
WHERE TableName = ' + QUOTENAME(QUOTENAME(@DbName) + '.' + QUOTENAME(SCHEMA_NAME(so.schema_id)) + '.' + QUOTENAME(so.name), '''') + N';
'
FROM [MyDb].sys.objects so
WHERE so.type = 'U'
    AND so.is_ms_shipped = 0;
-- 执行拼接完成的动态SQL
EXEC sp_executesql @DynamicSQL;
-- 输出最终统计结果
SELECT * FROM #FinalResult;
-- 清理临时表
DROP TABLE #FinalResult;
注意事项
  • 对无唯一索引的大表执行全列DISTINCT统计会产生较高的IO与CPU开销,建议在业务低峰时段执行
  • 如果需要统计的是指定业务列的去重数而非整行去重,只需把代码中拼接COUNT(DISTINCT 列名)的部分替换为对应业务列即可
  • 原有查询获取的总记录数是系统视图的近似值,如果需要和实际去重数精度完全匹配,可将TotalRowCount也改为实际扫描统计的数值,会额外增加少量执行开销

内容的提问来源于stack exchange,提问作者William DeMasi

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.16 07:15:06