Return to Revisions

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.sstatic.net/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.sstatic.net/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)