Select all columns which are NULL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select all columns which are NULL

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-06-13 : 12:11:10

I have some tables for conversion that i need to clean up before i set up the SSIS imports. I want to delete all columns from the source tables which have all NULL values in their rows. Is there a select i can run that shows the column names which match this?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 12:22:28

select 'col1'
where not exists (select * from mytbl where col1 is not null)

Now you want that ststement for every column in the table
something like (might have some names wrong and wrong quotes)

select
'select ''' + column_name + ''' where not exists (select * from mytbl where ' + column_name + ' is not null)'
from information_schema.columns where table_name = 'mytbl'

now for every table (with identifier delimiters)

select
'select ''[' + table_name + '].[' + column_name + ']'' where not exists (select * from [' + table_name + '] where [' + column_name + '] is not null)'
from information_schema.columns
where table_type = 'base table'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-06-13 : 13:09:35

With the folowing sql, columns only null will have value 0, otherwise 1.


select sign(sum(case when col1 is null then 0 else 1 end)) as col1
      ,sign(sum(case when col2 is null then 0 else 1 end)) as col2
      ,sign(sum(case when col3 is null then 0 else 1 end)) as col3
...
  from table

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-06-13 : 13:55:05

wow. seems like something which would be utilized fairly frequently. that would be why MS hasn't integrated a single command to accomplish this.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-13 : 15:17:07

quote:
wow. seems like something which would be utilized fairly frequently.

Only if you have lots of nullable columns. Make your columns not null, hey, look at that, no reason to have such a feature.

quote:
that would be why MS hasn't integrated a single command to accomplish this.

I'm sure Oracle has such a feature for 4-5x the price.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 15:18:23

hmm...why do you need to delete columns each time? why cant you check for values in column and handle this in your ssis package data flow instead to determine whether they should be considered or not.
If you can explain scenario, may be we can suggest a better approach

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-14 : 04:17:12

quote:
Originally posted by WJHamel

wow. seems like something which would be utilized fairly frequently. that would be why MS hasn't integrated a single command to accomplish this.

Probably not as often as yoou would think. I don't think I've had to do this in 15+ years.
Not sure what the point would be.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources