Count number of NULL values in each column in SQL

Question

I am trying to write a script that will show the number of non-null values in each column as well as the total number of rows in the table.

I have found a couple ways to do this:

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;

and

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

But these require me to type in each column name manually. Is there a way to perform this action for each column without listing them?

Answer 1

You should use 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