How to detect and remove a column that contains only null values?

Question

In my table table1 there are 6 columns Locations,a,b,c,d,e.

Locations [a]   [b]   [c]  [d]   [e]

[1]       10.00 Null  Null 20.00 Null

[2]       Null  30.00 Null Null  Null

i need the result like

Locations [a]   [b]   [d]

[1]       10.00 Null  20.00

[2]       Null  30.00 Null

My question is how to detect and delete column that contains all null values using sql query. Is it possible?

If yes then please help and give sample.

Answer 1

Here is a fast (and ugly) stored proc that takes the name of the table and print (or drop if you want it to) the fields that are full of nulls.

ALTER procedure mysp_DropEmptyColumns 
  @tableName nvarchar(max)
as begin
  declare @FieldName nvarchar(max)
  declare @SQL nvarchar(max)
  declare @CountDef nvarchar(max)
  declare @FieldCount int

  declare fieldNames cursor  local fast_forward for
    select c.name
      from syscolumns c 
        inner join sysobjects o on c.id=o.id
      where o.xtype='U'
        and o.Name=@tableName

  open fieldNames 
  fetch next from fieldNames into @FieldName
  while (@@fetch_status=0)
  begin
    set @SQL=N'select @Count=count(*) from "'+@TableName+'" where "'+@FieldName+'" is not null'
    SET @CountDef = N'@Count int output';
    exec sp_executeSQL @SQL, @CountDef, @Count = @FieldCount output
    if (@FieldCount=0)
    begin
      set @SQL = 'alter table '+@TableName+' drop column '+@FieldName
      /* exec sp_executeSQL @SQL */
      print @SQL
    end
    fetch next from fieldNames into @FieldName
  end

  close fieldNames
end

This uses a cursor, and is a bit slow and convoluted, but I suspect that this is a kind of procedure that you'll be running often

Answer 2

SQL is more about working on rows rather than columns.

If you're talking about deleting rows where c is null, use:

delete from table1 where c is null

If you're talking about dropping a column when all rows have null for that column, I would just find a time where you could lock out the DB from users and execute one of:

select c from table1 group by c
select distinct c from table1
select count(c) from table1 where c is not null

Then, if you only get back just NULL (or 0 for that last one), weave your magic (the SQL Server command may be different):

alter table table1 drop column c

Do this for whatever columns you want.

You really need to be careful if you're deleting columns. Even though they may be full of nulls, there may be SQL queries out there that use that column. Dropping the column will break those queries.