Return All the Rows From A Table Where At Least One Of The Columns IS NULL

Sometimes you inherit a table/database and you need to check if any of the rows in the table have columns with null values
I will show you a great timesaver to accomplish this task
First let's create the table

CREATE TABLE TestNulls (c1 INT,c2 INT, c3 INT,c4 INT,c5 INT, c6 INT)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,NULL,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (1,1,1,NULL,1,1)

One way to check is to write an OR for every column
SELECT *
FROM TestNulls
WHERE C1 IS NULL
OR C2 IS NULL
OR C3 IS NULL
OR C4 IS NULL
OR C5 IS NULL
OR C6 IS NULL

A better (faster) way is to do it like this

SELECT *
FROM TestNulls
WHERE C1+C2+C3+C4+C5+C6 IS NULL

What if you have 50 columns, who wants to write them all out? Well not me.
No problem in Query Analyzer hit F8, this will bring up the Object Browser
In SQL Server Managment Studio use the Object Explorer
Navigate to the table click on the + and drag the whole column folder into the query window
You will see something like this c1, c2, c3, c4, c5, c6
Copy that open up notepad and paste it into notepad. Hit CTRL + H and in the Find box type a , and in the Replace box type a +, hit OK. After that do a CTRL + A and CTRL + C
Paste it back into Query Analyzer/SQL Server Managment Studio and you are done with that part

This will work everytime if all the columns are integers, what if you have varchars?
Let's test that out


CREATE TABLE TestNullsChar (c1 CHAR(1),c2 CHAR(1), c3 CHAR(1),c4 CHAR(1),c5 CHAR(1), c6 CHAR(1))
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,NULL,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,1,1,1)
INSERT TestNullsChar VALUES (1,1,1,NULL,1,1)


No problem here (depending on your CONCAT_NULL_YIELDS_NULL setting)

SELECT *,C1+C2+C3+C4+C5+C6
FROM TestNullsChar
WHERE C1+C2+C3+C4+C5+C6 IS NULL

Let's set our CONCAT_NULL_YIELDS_NULL setting to off
SET CONCAT_NULL_YIELDS_NULL OFF

Run the query again

SELECT *,C1+C2+C3+C4+C5+C6
FROM TestNullsChar
WHERE C1+C2+C3+C4+C5+C6 IS NULL


As you can see nothing was returned so make sure that CONCAT_NULL_YIELDS_NULL is set to on before executing such a query because NULL + any other value will return NULL if CONCAT_NULL_YIELDS_NULL is set to on

SET CONCAT_NULL_YIELDS_NULL ON