动态SQL查询以查找表所有列的缺失值计数

试试这个：

DECLARE @sql nvarchar(max) = N'SELECT';
DECLARE @table_name nvarchar(256) = N'YourTableName'

SELECT @sql = @sql + ' COUNT(*)-COUNT(' + QUOTENAME(COLUMN_NAME) + ') as '+ QUOTENAME(COLUMN_NAME) + N','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
  AND TABLE_NAME = @table_name

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from ' + @table_name

EXEC (@sql)

添加

好吧，所以这是一个示例，说明如何对每列使用相似的方法来计算至少具有一个重复项的值的数量：

DECLARE @sql nvarchar(max) = N'WITH duplicates AS (SELECT';
DECLARE @table_name nvarchar(256) = N'YourTableName';

SELECT @sql = @sql 
  + N' CASE WHEN COUNT(' + QUOTENAME(COLUMN_NAME) 
  + N') OVER (PARTITION BY ' + QUOTENAME(COLUMN_NAME) 
  + N') > 1 THEN ' + QUOTENAME(COLUMN_NAME)  
  + N' END as '+ QUOTENAME(COLUMN_NAME) + N','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
  AND TABLE_NAME = @table_name;

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from ' + @table_name + ') SELECT';

SELECT @sql = @sql 
  + N' COUNT(DISTINCT ' + QUOTENAME(COLUMN_NAME) 
  + N') as '+ QUOTENAME(COLUMN_NAME) + N','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'dbo'
  AND TABLE_NAME = @table_name;

SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' from duplicates';

EXEC (@sql);

您会看到它使用了2条SELECT语句，第一个创建了一个CTE，该CTE将所有没有重复的值替换为NULL：

CASE WHEN COUNT([C1]) OVER (PARTITION BY [C1]) > 1 THEN [C1] END as [C1]

第二个使用COUNT DISTINCT计数剩余的内容。这样，将不计算NULL值。