sql - 计算SQL中每列中NULL值的数量

Question

我正在尝试编写一个脚本，该脚本将显示每列中非空值的数量以及表中的总行数。

我找到了几种方法来做到这一点:

SELECT sum(case my_column when null then 1 else 0) "Null Values",
   sum(case my_column when null then 0 else 1) "Non-Null Values"
FROM my_table;

和

SELECT count(*) FROM my_table WHERE my_column IS NULL 
UNION ALL
SELECT count(*) FROM my_table WHERE my_column IS NOT NULL

但是这些需要我手动输入每个列名。有没有办法对每列执行此操作而不列出它们？

Answer 1

您应该使用 execute :

DECLARE @t nvarchar(max)
SET @t = N'SELECT '

SELECT @t = @t + 'sum(case when ' + c.name + ' is null then 1 else 0 end) "Null Values for ' + c.name + '",
                sum(case when ' + c.name + ' is null then 0 else 1 end) "Non-Null Values for ' + c.name + '",'
FROM sys.columns c 
WHERE c.object_id = object_id('my_table');

SET @t = SUBSTRING(@t, 1, LEN(@t) - 1) + ' FROM my_table;'

EXEC sp_executesql @t