Is there a quick way to check if ANY column is NULL?

Question

I have a table with around 20 columns. Aside from typing out:

Where column1 is null OR column2 is null OR column3 is null etc...

Is there a quicker way to just check every column and see if any value is null and if so, return that record?

Answer 1

No. There are ways to code it quicker, but there are no shortcuts like you imply. Taken from an answer I gave on dba.stackexchange:

DECLARE @tb NVARCHAR(255), @sql NVARCHAR(MAX);

SET @tb = N'dbo.[table]';

SET @sql = N'SELECT * FROM ' + @tb + ' WHERE 1 = 0';

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

EXEC sp_executesql @sql;

Answer 2

You can find the column names using something like this:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = <table_name> 

Then, I would write a procedure using this, and that would loop through the entries in your table and the column names.

Source: http://codesnippets.joyent.com/posts/show/337