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?

Why do you want to do this? You can create a script that will look up the names of all columns from the schema views and construct the queries but it's ugly. What problem are you trying to solve? Unless you are writing some kind of admin script, there are probably better ways to solve the original problem — Panagiotis Kanavos, Jun 25 '14 at 14:23
I want to be able to see how many of the values in each column are already populated. — TaiwanTimmy, Jun 25 '14 at 14:26
your first way is the right approach. explicitly name the columns, most of the time that's what we have to do when writing SELECTS anyway. your other option i guess is some funky dynamic sql and that will be ugly — g2server, Jun 25 '14 at 14:28
@TaiwanTimmy `already populated` is a sign that you are probably storing two or more entities in a single row. Perhaps you should break your table in two or more tables (eg a master-detail schema). It will be far easier and faster to join two tables than create a dynamic sql statement — Panagiotis Kanavos, Jun 25 '14 at 14:37
here is a little example for [retrieving column names from a certain table in a certain database](http://stackoverflow.com/a/23522270/2186023) — DrCopyPaste, Jun 25 '14 at 14:42

Alireza · Answer 1 · 2014-06-26T11:00:47.577

10

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

edited Jun 26 '14 at 11:00

answered Jun 25 '14 at 14:47

Alireza

4,976
1
23
36

Scratch that, it isn't summing the number NULLs correctly. Each column results in 0 NULLs and all Non NULLs. Ideas? – TaiwanTimmy Jun 25 '14 at 15:35
1

The problem is with the case statements. Change both of them to this `case when ' + c.name + ' is null` instead of `case ' + c.name + ' when null`. This will return the correct counts. – SQLChao Jun 25 '14 at 16:12
Thanks @JChao. I missed that at the last moment – Alireza Jun 25 '14 at 20:36
Hey! Is there a command to select the null values from each column at once? – Laxmi Agarwal Jan 20 '22 at 07:56
@LaxmiAgarwal This script selects number of null and non-null values for all columns. Or maybe I didn't understand your question – Alireza Jan 21 '22 at 12:56
1

@Alireza Thank you for replying. My query is solved. I have used the count and group by functions to check the null values. – Laxmi Agarwal Jan 25 '22 at 11:26

score 3 · Accepted Answer · answered Jun 25 '14 at 14:42

3

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);

answered Jun 25 '14 at 14:42

Richard Hansell

5,315
1
16
35

Thanks, but this just results with `Command(s) completed successfully` How do I go about displaying the table? – TaiwanTimmy Jun 25 '14 at 15:37
You would need to change the name of the table (on the very first line) to the name of the table in your database... – Richard Hansell Jun 25 '14 at 15:46
It given the same response after changing it to the correct table name – TaiwanTimmy Jun 25 '14 at 16:02
Is your table in a schema other than "dbo"? Also, note that if you run this from a different database you will need to prefix the sys.tables and sys.columns with the database name, e.g. [MyDatabase].sys.tables. I ran it and it worked fine for me... the results just appear in the Results tab of SSMS. – Richard Hansell Jun 25 '14 at 16:05
I tried it on a different table that I built for testing and it worked fine. Thanks again – TaiwanTimmy Jun 25 '14 at 18:07
@RichardHansell Attempting to convert this into Snowflake SQL and wondered why you needed three single-quotes after the SELECT in the SQL you are building? – Rick Pack Aug 17 '22 at 00:53
@RichardHansell Ah, I see now : you are surrounding the column name with quotation marks. – Rick Pack Aug 17 '22 at 01:38
Yes, it's the easiest way to escape quotes when building a string in SQL Server – Richard Hansell Aug 17 '22 at 15:04

score 1 · Answer 3 · answered Jun 25 '14 at 14:23

1

may be this 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

answered Jun 25 '14 at 14:23

mohan111

8,633
4
28
55

3

`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?` Isn't that exactly what you're doing here? – LittleBobbyTables - Au Revoir Jun 25 '14 at 14:25

score 1 · Answer 4 · edited May 23 '17 at 12:32

1

you can go with dynamic sql and sys tables.
depending on the version of sql you are using the syntax will change slightly but these are the steps:
- list the columns reading sys.columns and save the list in a temp table or table variable
- make a loop through that temp table and build the sql using the same logic you would apply manually
- execute the dynamic sql built on previous step with sp_executesql

edited May 23 '17 at 12:32

Community

1
1

answered Jun 25 '14 at 14:33

Paolo

2,224
1
15
19

how would you structure the loop? – Mariah Akinbi Apr 27 '19 at 00:32

Count number of NULL values in each column in SQL

4 Answers4

Linked