如何在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
相关产品推荐
相关产品推荐

