Test if any fields are NULL

Question

I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.

I need something like

SELECT * FROM table AS t WHERE ANY(t.* IS NULL)

I don't want to have to do

SELECT * FROM table AS t WHERE t.c1 = NULL OR t.c2 = NULL OR t.c3 = NULL

This would be a HUGE query.

Answer 1

An extension to @db2's answer with less (read:zero) hand-wrangling:

DECLARE @tb NVARCHAR(255) = N'dbo.[table]';

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + @tb
    + ' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@tb);

EXEC sp_executesql @sql;

Answer 2

There's no nice built-in syntax, but Management Studio has a couple convenient features to generate the query quickly.

In Object Explorer, drill down to the table you want, expand it, then drag the whole "Columns" folder into a blank query editor. This will add a comma-separated list of columns to the query.

Next, open up Find And Replace. Set "Find What" to , and set "Replace With" to IS NULL OR (with a leading space) then hit Replace All. You'll have to clean up the last one in the sequence by hand.

It's still ugly, but it's less labor intensive ugly.

Answer 3

You should list out all the columns as per JNK's comment.

WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL

A somewhat less efficient approach that avoids this is below though.

;WITH xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' AS ns) 
SELECT * 
FROM   YourTable AS T1 
WHERE (
    SELECT T1.* 
    FOR XML PATH('row'), ELEMENTS XSINIL, TYPE
  ).exist('//*/@ns:nil') = 1 

(Based on this SO answer)