Find columns that contain only zeros

Question

I'm working with SQL Server 2008. I have a list of column names on a table and I'd like to know how to use SQL to return the names of those columns which contain nothing but zero or NULL values.

Answer 1

declare @T table
(
  Col1 int,
  Col2 int,
  Col3 int,
  Col4 int
)

insert into @T values
(1,   0   , null, null),
(0,   null, 0   , 1)

select U.ColName
from
  (
    select count(nullif(Col1, 0)) as Col1,
           count(nullif(Col2, 0)) as Col2,
           count(nullif(Col3, 0)) as Col3,
           count(nullif(Col4, 0)) as Col4
    from @T
  ) as T
unpivot
  (C for ColName in (Col1, Col2, Col3, Col4)) as U
where U.C = 0

Result:

ColName
----------
Col2
Col3

The idea behind this is to count the non null values and only keep those with a count of 0.

COUNT will only count non null values.
NULLIF(ColX, 0) will make all 0 into null.
The inner query returns one row with four columns. UNPIVOT will turn it around so you have two columns and four rows.
Finally where U.C = 0 makes sure that you only get the columns that has no values other than null or 0.