The number of NULL numbers in each SQL column

Question

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

I found several 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 this requires that I enter the name of each column manually. Is there a way to perform this action for each column without listing them?

Answer 1

As Paolo said, but here is an example:

 DECLARE @TableName VARCHAR(512) = 'invoiceTbl'; DECLARE @SQL VARCHAR(1024); WITH SQLText AS ( SELECT ROW_NUMBER() OVER (ORDER BY c.Name) AS RowNum, 'SELECT ''' + c.name + ''', SUM(CASE WHEN ' + c.Name + ' IS NULL THEN 1 ELSE 0 END) AS NullValues FROM ' + @TableName AS SQLRow FROM sys.tables t INNER JOIN sys.columns c ON c.object_id = t.object_id WHERE t.name = @TableName), Recur AS ( SELECT RowNum, CONVERT(VARCHAR(MAX), SQLRow) AS SQLRow FROM SQLText WHERE RowNum = 1 UNION ALL SELECT t.RowNum, CONVERT(VARCHAR(MAX), r.SQLRow + ' UNION ALL ' + t.SQLRow) FROM SQLText t INNER JOIN Recur r ON t.RowNum = r.RowNum + 1 ) SELECT @SQL = SQLRow FROM Recur WHERE RowNum = (SELECT MAX(RowNum) FROM Recur); EXEC(@SQL); 

Answer 2

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 

Answer 3

maybe it works

 select count(case when Column1 is null then 1 end) as Column1NullCount, count(case when Column2 is null then 1 end) as Column2NullCount, count(case when Column3 is null then 1 end) as Column3NullCount ... from My_Table 

Answer 4

you can use dynamic sql and sys tables.
depending on the sql version you are using, the syntax will change a little, but these are the following steps:
- list the columns by reading sys.columns and save the list in temp table or table variable
- loop through this temporary table and build sql using the same logic that you applied manually
- execute dynamic sql built in the previous step using sp_executesql