Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Listing out the fields or using dynamic SQL are your options. There's not really a way to evaluate every field at the same time since this is counter to the idea of a database (all the fields mean something different). –  JNK Mar 12 '12 at 20:17
    
What do you mean by "empty"? The empty string? i.e. '' –  Nick Chammas Mar 12 '12 at 22:40
add comment

3 Answers 3

up vote 7 down vote accepted

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;
share|improve this answer
    
+1 - May want to throw braces around the column name just in case –  JNK Mar 12 '12 at 20:49
    
Done so, thanks –  Aaron Bertrand Mar 12 '12 at 20:50
add comment

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)

share|improve this answer
1  
You can improve the XQuery part a bit by doing .exist('//*/@ns:nil') = 1 instead of .value.... –  Mikael Eriksson Mar 12 '12 at 21:54
    
@MikaelEriksson - Thanks for the tip. Done! –  Martin Smith Mar 12 '12 at 21:59
    
+1 This worked wonders. Only had 1.1k rows to check for. FYI this also evaluates the textual version of NULL. –  GoldBishop Apr 29 at 15:00
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.