Select all columns except those with only null values

Question

Is there a way to select the column names of a certain table except those columns with only null values without knowing how many columns the table have.

-------------------------
| col1  | col2   | col3 |
------------------------
| val1  | null   | val2 |
| val1  | null   | null |
| null  | null   | val2 |
-------------------------

Should result in:

------------------------------------
| cols_except_those_with_null_only |
-----------------------------------
| col1                             |
| col3                             |
------------------------------------

Thanks!

Answer 1

Create a stored procedure with following content:

create table #cols (colname varchar(255), nullCount int)

insert into #cols (colname)
select name from syscolumns where id = object_id('tblTest')

declare @c varchar(255)

declare curCols cursor for select colname from #cols
open curCols
fetch next from curCols into @c
while @@fetch_status = 0 begin
  exec ('update #cols set nullCount = (select count(*) from tblTest where ' + @c + ' is not null) where colname = ''' + @c + '''')
  fetch next from curCols into @c
end
close curCols
deallocate curCols

declare @rv table (cols_expect_those_with_null_only varchar(255))

insert into @rv (cols_expect_those_with_null_only)
select colname from #cols
where nullCount > 0

drop table #cols

select * from @rv