How can I find all columns filled with 100% zeros in my SQL Server database schema?

Question

Is there an SQL way to find out which columns in my schema are completely filled with null values? There are several fields in several tables that, as I know, are not used by the application and will be deleted, but I would like to know if there was an automatic / script method to find this throughout the database to find candidates for code review / possible deletion .

Running SQL Server 2005 on x86, if that matters.

Thanks in advance!

Answer 1

create table #SuspectColumns ( TABLE_SCHEMA sysname, TABLE_NAME sysname, COLUMN_NAME sysname ) declare csrColumns cursor fast_forward for select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE = 'YES' declare @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname, @sql nvarchar(max) open csrColumns while (1=1) begin fetch next from csrColumns into @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME if @@FETCH_STATUS<>0 break set @sql = N'if not exists(select 1 from ' + QUOTENAME(@TABLE_SCHEMA) + N'.' + QUOTENAME(@TABLE_NAME) + N' where ' + QUOTENAME(@COLUMN_NAME) + N'is not null) insert into #SuspectColumns values (''' + @TABLE_SCHEMA + N''',''' + @TABLE_NAME + N''',''' + @COLUMN_NAME + N''')' exec sp_executesql @sql end /* while */ close csrColumns deallocate csrColumns select * from #SuspectColumns drop table #SuspectColumns 

Answer 2

you can return max (column) and check for zeros

Answer 3

Above, I believe the following SQL should work. It will execute a query for each combination of columns and columns, and the query will return the table name and column name if there are no rows or all zero rows in this combination of columns and columns.

 DECLARE @table_columns TABLE ( table_name nvarchar(128), column_name nvarchar(128) ); DECLARE @table_name nvarchar(128); DECLARE @column_name nvarchar(128); INSERT INTO @table_columns(table_name, column_name) select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.columns; while (select count(*) from @table_columns) > 0 begin select top 1 @table_name = table_name, @column_name = column_name from @table_columns exec('SELECT ''' + @table_name + ''' as table_name, ''' + @column_name + ''' as column_name WHERE NOT EXISTS (SELECT TOP 1 * FROM ' + @table_name + ' WHERE ' + @column_name + ' IS NOT NULL)') delete from @table_columns where table_name = @table_name and column_name = @column_name end