SQL query to find columns having at least one non null value

I would not recommend using count(distinct) because it incurs overhead for removing duplicate values. You can just use count().

You can construct the query for counts using a query like this:

select count(col1) as col1_cnt, count(col2) as col2_cnt, . . .
from t;

If you have a list of columns you can do this as dynamic SQL. Something like this:

declare @sql nvarchar(max);

select @sql = concat('select ',
                     string_agg(concat('count(', quotename(s.value), ') as cnt_', s.value),
                      ' from t'
                    )
from string_split(@list) s;

exec sp_executesql(@sql);

This might not quite work if your columns have special characters in them, but it illustrates the idea.

You should probably use exists since you aren't really needing a count of anything.

You don't indicate how you want to consume the results of multiple counts, however one thing you could do is use concat to return a list of the columns meeting your criteria:

The following sample table has 5 columns, 3 of which have a value on at least 1 row.

create table t (col1 int, col2 int, col3 int, col4 int, col5 int)
insert into t select null,null,null,null,null
insert into t select null,2,null,null,null
insert into t select null,null,null,null,5
insert into t select null,null,null,null,6
insert into t select null,4,null,null,null
insert into t select null,6,7,null,null

You can name the result of each case expression and concatenate, only the columns that have a non-null value are included as concat ignores nulls returned by the case expressions.

select Concat_ws(', ',
    case when exists (select * from t where col1 is not null) then 'col1' end,
    case when exists (select * from t where col2 is not null) then 'col2' end,
    case when exists (select * from t where col3 is not null) then 'col3' end,
    case when exists (select * from t where col4 is not null) then 'col4' end,
    case when exists (select * from t where col5 is not null) then 'col5' end)

Result:

col2, col3, col5

I asked a similar question about a decade ago. The best way of doing this in my opinion would meet the following criteria.

• Combine the requests for multiple columns together so they can all be calculated in a single scan.
• If the scan encounters a not null value in every column under consideration allow it to exit early without reading the rest of the table/index as reading subsequent rows won't change the result.

This is quite a difficult combination to get in practice.

The following might give you the desired behaviour

SELECT DISTINCT TOP 2 ColumnWithoutNull
FROM YourTable 
CROSS APPLY (VALUES(CASE WHEN b IS NOT NULL THEN 'b' END),
                   (CASE WHEN c IS NOT NULL THEN 'c' END)) V(ColumnWithoutNull)
WHERE ColumnWithoutNull IS NOT NULL
OPTION ( HASH GROUP, MAXDOP 1, FAST 1)

If it gives you a plan like this

Hash match usually reads all its build input first meaning that no shortcircuiting of the scan will happen. If the optimiser gives you an operator in "flow distinct" mode it won't do this however and the query execution can potentially stop as soon as TOP receives its first two rows signalling that a NOT NULL value has been found in both columns and query execution can stop.

But there is no hint to request the mode for hash aggregate so you are dependent on the whims of the optimiser as to whether you will get this in practice. The various hints I have added to the query above are an attempt to point it in that direction however.