SQL Server 2017 如何遍历全库表与字段统计各列Top值
SQL Server 2017 全库各字段高频Top值批量统计实现
实现逻辑
这套方案完全依托你已经掌握的两个基础方法搭建,不需要额外引入高版本SQL语法:
- 沿用
INFORMATION_SCHEMA.COLUMNS接口拉取全库所有用户表的字段元数据清单 - 完全复用你已经写好的单字段Top值统计逻辑,通过动态SQL拼接逐字段执行统计,最终把所有结果统一归集,不用手动挨个表跑查询
可直接落地的存储过程代码
以下代码适配SQL Server 2017环境,自动过滤无法支持GROUP BY的特殊大字段类型,每条结果会标注对应所属的库、架构、表、列信息,避免多结果混杂分不清来源:
CREATE OR ALTER PROCEDURE sp_AllColumnTopValueStats @TopN INT = 100, -- 每个字段需要统计的高频值Top数量,和你原有逻辑的top(100)对齐 @ExcludeSystemTables BIT = 1 -- 是否排除系统表/系统架构对象,默认排除 AS BEGIN SET NOCOUNT ON; -- 初始化结果集表结构 IF OBJECT_ID('tempdb..#AllColumnStatsResult') IS NOT NULL DROP TABLE #AllColumnStatsResult; CREATE TABLE #AllColumnStatsResult( DatabaseName SYSNAME, SchemaName SYSNAME, TableName SYSNAME, ColumnName SYSNAME, TopValue SQL_VARIANT, OccurrenceCount BIGINT ); -- 拼接所有需要执行的单字段统计SQL DECLARE @DynamicSQL NVARCHAR(MAX) = N''; SELECT @DynamicSQL = @DynamicSQL + N' INSERT INTO #AllColumnStatsResult(DatabaseName, SchemaName, TableName, ColumnName, TopValue, OccurrenceCount) SELECT TOP (@TopN) ' + QUOTENAME(DB_NAME(), '''') + N' AS DatabaseName, ' + QUOTENAME(TABLE_SCHEMA, '''') + N' AS SchemaName, ' + QUOTENAME(TABLE_NAME, '''') + N' AS TableName, ' + QUOTENAME(COLUMN_NAME, '''') + N' AS ColumnName, ' + QUOTENAME(COLUMN_NAME) + N' AS TopValue, COUNT(*) AS OccurrenceCount FROM ' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) + N' GROUP BY ' + QUOTENAME(COLUMN_NAME) + N' ORDER BY COUNT(*) DESC; ' FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME WHERE -- 只统计用户基表,排除视图 t.TABLE_TYPE = 'BASE TABLE' -- 排除不支持GROUP BY的特殊字段类型 AND c.DATA_TYPE NOT IN ('text', 'ntext', 'image', 'xml', 'hierarchyid', 'geometry', 'geography') -- 根据参数控制是否排除系统架构 AND ( @ExcludeSystemTables = 0 OR (c.TABLE_SCHEMA NOT IN ('sys', 'INFORMATION_SCHEMA', 'cdc', 'dtp')) ); -- 执行拼接好的全量统计SQL EXEC sp_executesql @stmt = @DynamicSQL, @params = N'@TopN INT', @TopN = @TopN; -- 输出最终全量统计结果 SELECT * FROM #AllColumnStatsResult ORDER BY SchemaName, TableName, ColumnName, OccurrenceCount DESC; END
使用方法
- 在目标数据库下执行上述代码创建存储过程
- 运行对应命令即可拿到全库所有字段的Top高频值:
-- 统计每个字段前100个高频值,和你原有单字段逻辑完全对齐 EXEC sp_AllColumnTopValueStats @TopN = 100
注意事项
- 全库逐字段统计会对磁盘IO、内存造成较大占用,尽量选业务低峰期执行,如果数据量太大可以临时修改元数据查询的过滤条件,只统计核心业务表
- 空值会被单独作为一类值统计计数,和你手动跑单字段查询的统计逻辑完全一致
- 所有表名、字段名都通过
QUOTENAME函数做了转义,哪怕是带特殊字符、SQL保留字的对象,也不会触发语法报错
内容的提问来源于stack exchange,提问作者Mr JaZ
相关产品推荐
相关产品推荐

