如何遍历数据库表列并输出各列去重值及对应计数至新表

需要将数据库中所有Schema、Table、Column，以及各列的去重值和对应计数输出至一张新表。已通过information_schema.columns获取Schema、Table、Column信息，但无法实现去重值及对应计数的输出——目前用Cursor仅能得到每列去重值的总数，实际需要按值分组输出具体值和计数，示例结果如下：

现有代码（无法满足需求） #

DECLARE @table_name AS NVARCHAR(128);
DECLARE @schema_name AS NVARCHAR(128);
DECLARE @column_name AS NVARCHAR(128);
DECLARE @distinct_count int;
DECLARE @sql AS NVARCHAR(MAX);

DECLARE tables_cursor CURSOR FOR
 SELECT table_schema, table_name, column_name
 FROM information_schema.columns
 ORDER BY TABLE_SCHEMA, table_name, column_name;

OPEN tables_cursor;  

FETCH NEXT FROM tables_cursor INTO @schema_name, @table_name, @column_name;

WHILE @@FETCH_STATUS = 0

BEGIN
 SET @sql = N'SELECT @count = COUNT(DISTINCT ' + @column_name + N') FROM ' + @schema_name + '.' + @table_name;

 EXEC sp_executesql @sql, N'@count INT OUTPUT', @count = @distinct_count OUTPUT;

 IF @schema_name <> 'sys' AND @table_name <> 'Tool1'
  BEGIN
    SELECT @schema_name AS tableschema_name, @table_name AS table_name, @column_name AS column_name, @distinct_count AS distinct_count;
    INSERT INTO dbo.Tool1 (SchemaName, TableName, ColumnName, [Count])
    SELECT 
        @schema_name AS SchemaName
        ,@table_name AS TableName
        ,@column_name AS ColumnName
        ,@distinct_count AS [Count];
  END

 FETCH NEXT FROM tables_cursor INTO @schema_name, @table_name, @column_name;

END

CLOSE tables_cursor;
DEALLOCATE tables_cursor;

解决方案代码 #

1. 确保目标表结构正确 #

先确认dbo.Tool1表存在且结构符合需求，若不存在则执行创建语句：

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'Tool1' AND schema_id = SCHEMA_ID('dbo'))
BEGIN
    CREATE TABLE dbo.Tool1
    (
        SchemaName NVARCHAR(128),
        TableName NVARCHAR(128),
        ColumnName NVARCHAR(128),
        ColumnGrouppedValue SQL_VARIANT, -- 兼容不同数据类型的列值
        ValueCount INT
    )
END

2. 修改Cursor逻辑实现分组统计 #

DECLARE @table_name AS NVARCHAR(128);
DECLARE @schema_name AS NVARCHAR(128);
DECLARE @column_name AS NVARCHAR(128);
DECLARE @sql AS NVARCHAR(MAX);

DECLARE tables_cursor CURSOR FOR
 SELECT table_schema, table_name, column_name
 FROM information_schema.columns
 WHERE table_schema <> 'sys' AND table_name <> 'Tool1' -- 提前过滤不需要的表
 ORDER BY TABLE_SCHEMA, table_name, column_name;

OPEN tables_cursor;  

FETCH NEXT FROM tables_cursor INTO @schema_name, @table_name, @column_name;

WHILE @@FETCH_STATUS = 0
BEGIN
 -- 生成动态SQL：分组查询列值和计数，并插入到Tool1
 SET @sql = N'
    INSERT INTO dbo.Tool1 (SchemaName, TableName, ColumnName, ColumnGrouppedValue, ValueCount)
    SELECT 
        ''' + @schema_name + N''' AS SchemaName,
        ''' + @table_name + N''' AS TableName,
        ''' + @column_name + N''' AS ColumnName,
        CAST(' + QUOTENAME(@column_name) + N' AS SQL_VARIANT) AS ColumnGrouppedValue,
        COUNT(*) AS ValueCount
    FROM ' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name) + N'
    WHERE ' + QUOTENAME(@column_name) + N' IS NOT NULL -- 可选：排除NULL值，按需调整
    GROUP BY ' + QUOTENAME(@column_name) + N'
 ';

 EXEC sp_executesql @sql;

 FETCH NEXT FROM tables_cursor INTO @schema_name, @table_name, @column_name;
END

CLOSE tables_cursor;
DEALLOCATE tables_cursor;

关键说明 #

• 使用QUOTENAME()函数处理对象名，避免特殊字符或关键字导致的语法错误
• 用SQL_VARIANT类型存储列值，兼容字符串、数字、日期等不同数据类型
• 提前在Cursor查询条件中过滤sys架构和Tool1表，减少循环次数
• 若需要统计NULL值，可移除WHERE ColumnName IS NOT NULL条件

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