选择除仅具有空值的那些列以外的所有列

Question

有没有一种方法可以选择某个表的列名，只有那些具有空值的列除外，而不知道该表有多少列。

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

应导致：

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

谢谢！

Answer 1

创建具有以下内容的存储过程：

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