SQL query to check if 100+ (or dynamic) columns in table are null

Question

Let’s assume a table has more than 100 columns and I want to display all the rows when all of its columns (Col1, Col2, …, ColN) are nulls except for the column(s) I mentioned (PK).

This is just a sample table I created to illustrate this question.

Sample Table with data

PK	Col1	Col2	Col3	Col4
1	45	12	171	15
2	13	NULL	15	NULL
3	17	14	130	NULL
4	10	11	110	12
5	45	15	185	54
6	NULL	NULL	NULL	NULL
7	16	48	NULL	25
8	NULL	NULL	NULL	NULL
9	13	45	41	64
10	NULL	NULL	NULL	NULL
11	21	12	165	56
12	NULL	NULL	NULL	NULL

In this case, the rows 6, 8, 10 and 12 (Column name: PK) are the result I want to see since all of the columns (Col1 to Col4) are null except for the PK column.

Desired Output

PK	Col1	Col2	Col3	Col4
6	NULL	NULL	NULL	NULL
8	NULL	NULL	NULL	NULL
10	NULL	NULL	NULL	NULL
12	NULL	NULL	NULL	NULL

The typical way to do this is to write a query like this.

SELECT * FROM TableName WHERE COALESCE(Col1, Col2, Col3, Col4) is null

If the above table has more 100 columns as I said, writing this query will be hard especially if you have to do it often.

Therefore, I have created a query like this.

DECLARE @query NVARCHAR(MAX);

SELECT @query = ISNULL(@query+', ','') + [name] 
                FROM  sys.columns 
                WHERE object_id = OBJECT_ID('TableName') 
                AND  [name] != 'PK';

SET @query = N'SELECT * FROM TableName WHERE COALESCE('+ @query +') IS NULL';

EXECUTE(@query)

Does this SQL code follow common best practices? How is the quality of this code? (Ex: Performance)

Answer 1

Does this SQL code follow common best practices?

No.

SET @query = N'SELECT * FROM TableName WHERE COALESCE('+ @query +') IS NULL';

1. It is common best practice to not use Select * in production code. One's apologetics might include 'Well, this is dynamic code ...' and if you're okay with that (or the customer is) then carry on.

2. I would use a Socratic method and ask what is the purpose of returning columns you know are null? Why wouldn't you write the query to only return the PK if all other columns are null?

How is the quality of this code?

It looks is fine.

I would suggest if you want a better evaluation, to provide sample objects / DDL to allow others to run it locally. Perhaps to evaluate the query plan.