如何在SQL Server 2008-2012中找出无唯一值的无用列

核心思路 #

要找出表中全为NULL或值完全重复的列，核心是对每一列统计两个关键指标：

• 非NULL值的总数：若为0，说明列全是NULL
• 非NULL值的唯一值数量：若为1，说明所有非NULL值完全相同

单表检测脚本 #

使用动态SQL自动生成每一列的检测逻辑，替换YourTableName为实际表名即可运行：

DECLARE @TableName NVARCHAR(128) = 'YourTableName';
DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL += 'SELECT ''' + COLUMN_NAME + ''' AS 无有效数据的列, '
              + 'CASE WHEN COUNT(' + QUOTENAME(COLUMN_NAME) + ') = 0 THEN ''全为NULL'''
              + ' WHEN COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) + ') = 1 THEN ''值完全相同'''
              + ' END AS 原因 '
              + 'FROM ' + QUOTENAME(@TableName) + ' '
              + 'HAVING COUNT(' + QUOTENAME(COLUMN_NAME) + ') = 0 OR COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) + ') = 1 '
              + 'UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName;

-- 移除末尾多余的UNION ALL
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);

EXEC sp_executesql @SQL;

脚本说明 #

1. 借助INFORMATION_SCHEMA.COLUMNS获取目标表的所有列名
2. 为每一列生成独立查询，通过COUNT()判断是否全NULL，COUNT(DISTINCT)判断值是否唯一
3. 用UNION ALL合并所有列的检测结果，最后执行动态生成的SQL

批量遍历全库所有表（可选） #

如果需要检测当前数据库所有表的无效列，使用以下脚本：

DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL += 'SELECT ''' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ''' AS 表名, '
              + '''' + COLUMN_NAME + ''' AS 无有效数据的列, '
              + 'CASE WHEN COUNT(' + QUOTENAME(COLUMN_NAME) + ') = 0 THEN ''全为NULL'''
              + ' WHEN COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) + ') = 1 THEN ''值完全相同'''
              + ' END AS 原因 '
              + 'FROM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' '
              + 'HAVING COUNT(' + QUOTENAME(COLUMN_NAME) + ') = 0 OR COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) + ') = 1 '
              + 'UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = DB_NAME();

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

EXEC sp_executesql @SQL;

注意事项 #

• 大表执行COUNT(DISTINCT)会有性能消耗，建议在业务低峰时段运行
• 若列类型为TEXT/NTEXT/IMAGE，COUNT(DISTINCT)会报错，可在WHERE子句中添加AND DATA_TYPE NOT IN ('text','ntext','image')排除这类列

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