SQL Query to check if 100+ (or Dynamic) columns in table is null

Question

Let assume a table has more than 100 of columns & 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 explain 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 |
+----+------+------+------+------+

Image: https://i.stack.imgur.com/z17GK.png

In this case, 6, 8, 10 and 12 rows (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 |
+----+------+------+------+------+

Image: https://i.stack.imgur.com/prwFo.png

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.