How can I find columns that contain one or more null values?

Question

I have a small question regarding SQL.
I have a table with 450 columns and I would like to check which of those columns contain at least one null value.
How can I do this?

Example:

Id A1   A2   A3   A4
1  NULL 1    5    6
2  4    NULL 2    1
3  3    4    5    7

should simply return A1 and A2.

Hooray. Someone's decided that because SQL tables bear a superficial resemblance to a spreadsheet, it should be treated the same. It's *very rare* for a table to need to be so wide, and especially if the type of data in each column is of the same "type" (such that you want to act on multiple columns in the same manner), it's an indication that the data model is broken. It probably should have been `Id`, `A` and `Value` columns where `A` would contain the *data* currently embedded in the column names and `Value` would be named for whatever e.g. `7` actually is. — Damien_The_Unbeliever, May 28 '15 at 14:23

score 2 · Answer 1 · edited May 23 '17 at 12:05

2

There's not a simple way to find columns with specific conditions; you generally need to check each column explicitly. There are ways to do it dynamically or you can just have a massive query with 450 comparisons.

Another way is to UNPIVOT the data:

SELECT Id, Col FROM 
(
    SELECT Id, Col, Val
    FROM 
       (SELECT Id, A1, A2, ...
       FROM pvt) p
    UNPIVOT
       (Val FOR Id IN 
          (A1, A2, ...)
    )AS unpvt
)
WHERE Val is NULL

If this is a common real-time need (and not just a one-time or batch need) a better long-term solution would be to change your data structure so that each "column" is a row along with the value:

Id  Col  Val
--- ---- ----
1   A1   NULL
1   A2   1
1   A3   5
1   A4   6
2   A1   4
2   A2   NULL

etc.

(Note that the above is essentially the output of UNPIVOT)

edited May 23 '17 at 12:05

Community

1
1

answered May 28 '15 at 14:18

D Stanley

149,601
11
178
240

"you generally need to check each column explicitly" this got me thinking: Perhaps it's faster to concatenate all fields and check if the result is `NULL`? – DaSourcerer May 28 '15 at 15:13
@DaSourcerer That won't tell you _which_ column is NULL. – D Stanley May 28 '15 at 15:16
Well, I understood OP's problem to essentially be solvable by a (beware, pseudocode) `SELECT * FROM t1 WHERE GROUP_CONCAT(t1.*) IS NULL`, so finding columns with *any* `NULL` values should be sufficient. – DaSourcerer May 29 '15 at 07:34

Sachu · Answer 2 · 2015-05-28T14:35:12.363

the below code is used by me in sql server

try

    DECLARE @dbname VARCHAR(100) = 'ur_Database'
DECLARE @schemaName VARCHAR(100) = 'dbo'
DECLARE @tableName VARCHAR(100) = 'ur_Table'
DECLARE @result TABLE (col VARCHAR(4000))

SELECT  @dbname dbname
        ,t.name tbl
        ,c.name col
INTO    #temp
FROM    sys.columns c
JOIN    sys.tables t ON 
        t.object_id = c.object_id
WHERE   c.is_nullable = 1
AND     t.name = @tableName

DECLARE @sql NVARCHAR(MAX) =
STUFF(
(
    SELECT  'UNION ALL SELECT CASE WHEN EXISTS (SELECT 1 FROM ' + @dbname + '.' + @schemaName + '.' + tbl + ' WHERE ' + col + ' IS NULL) THEN '''+ @schemaName  + '.' + tbl + '.' + col+''' END AS NULL_Value_Exists '
    FROM    #temp
    FOR     XML PATH('')
), 1, 10, '   ')

INSERT @result
EXEC(@sql)

SELECT  *
FROM    @result
WHERE   col IS NOT NULL

DROP TABLE #temp

table data

columns in the table having null

That will tell you which _rows_ have a null value but not which _columns_. — D Stanley, May 28 '15 at 14:26
@DStanley u r write i misread the question..this will do his requirement — Sachu, May 28 '15 at 14:35

How can I find columns that contain one or more null values?

2 Answers2