Find columns containing only zeros

Question

I am working with SQL Server 2008. I have a list of column names in a table, and I would like to know how to use SQL to return the names of these columns that contain nothing but null or null.

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 UC = 0 

Result:

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

The idea is to count non- null values ​​and only contain those that have a counter of 0 .

COUNT will only read non-zero values.
NULLIF (ColX, 0) will do all 0 in null .
The internal query returns a single row with four columns. UNPIVOT will expand, so you have two columns and four rows.
Finally, where UC = 0 ensures that you only get columns that have no values ​​other than null or 0 .

Answer 2

Here is a brute force path, since you know all the column names.

 CREATE TABLE dbo.splunge ( a INT, b INT, c INT, d INT ); INSERT dbo.splunge VALUES (0,0,1,-1), (0,NULL,0,0), (0,0,0,NULL); SELECT cols = STUFF( CASE WHEN MIN(COALESCE(a,0)) = MAX(COALESCE(a,0)) THEN ',a' ELSE '' END + CASE WHEN MIN(COALESCE(b,0)) = MAX(COALESCE(b,0)) THEN ',b' ELSE '' END + CASE WHEN MIN(COALESCE(c,0)) = MAX(COALESCE(c,0)) THEN ',c' ELSE '' END + CASE WHEN MIN(COALESCE(d,0)) = MAX(COALESCE(d,0)) THEN ',d' ELSE '' END, 1, 1, '') FROM dbo.splunge; -- result: -- a,b GO DROP TABLE dbo.splunge; 

You could probably generate most of this script instead of doing it manually, assuming you know the naming scheme or data type of the columns you want (or just leaving the where clause completely and deleting the columns you want to manually).

 SELECT CHAR(13) + CHAR(10) + ' + CASE WHEN MIN(COALESCE(' + name + ',0)) = ' + 'MAX(COALESCE(' + name + ',0)) THEN '',' + name + ''' ELSE '''' END' FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.splunge') -- AND system_type_id = 56 -- AND name LIKE '%some pattern%' ; 

The result will look like the middle of the first request, so you can copy and paste and then delete the first + and add the surrounding STUFF and the request ...

Answer 3

Here we use a method that works for any table:

 declare @tableName nvarchar(max) = N'myTable' declare @columnName nvarchar(max) create table #zeros (column_name varchar(max)) declare c cursor local forward_only read_only for select column_name from information_schema.COLUMNS WHERE table_name = @tableName open c fetch next from c into @columnName while @@FETCH_STATUS = 0 begin declare @retval int declare @sql nvarchar(max) = N'set @retval = (select count(*) from ' + @tableName + N' where coalesce(' + @columnName + N', 0) <> 0)' exec sp_executesql @sql, N'@retval int out', @ retval=@retval out select @retval if @retval = 0 begin insert into #zeros (column_name) values (@columnName) end fetch next from c into @columnName end close c deallocate c select * from #zeros drop table #zeros