CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Snowflake
Snowflake

Reputation: 3081

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

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.

Upvotes: 0

Views: 299

Answers (2)

Sachu
Sachu

Reputation: 7766

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

Upvotes: 0

D Stanley
D Stanley

Reputation: 152556

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)

Upvotes: 2

Related Questions