SQL statement that lists all columns with just null

Question

Post reply

SQL statement that lists all columns with just null

el0

Valued Member

Points: 71
More actions
November 11, 2009 at 7:38 am

#213646

I want to help with creating a T-SQLstatment(SQL if possible) that list ALL Columns that just contains the NULL value.
I don't even now where to begin on this one.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Please provide a more information. What do you mean with "lists all columns that contains NULL"?

Please provide some sample data and a desired result for those data. You can find a link in my signature which should help to create those sample information

Thanks

Flo

el0 Valued Member Points: 71 More actions · Answer 2

table things:

ID int <-key

Size int

Type string

Comment string

Color string

ColumnThatDoNothing string

ColumnX int

...

and many hundred columns more.

In all posts thats included in the db, the ColumnThatDoNothing and ColumnX contain nothing at all(and many more).

They are most likely still there since the building of the db in the first place.

I want with a sql statment get the columns that contain nothing thru all the post in the db( some thousands)

Is this possible?

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Still not sure if I got you...

Do you mean something like this:

DECLARE @t TABLE (Id INT IDENTITY PRIMARY KEY, SomeInt INT, SomeDate DATE);

INSERT INTO @t

SELECT 1, NULL

UNION ALL SELECT NULL, NULL;

SELECT

COUNT(*)

,CASE

WHEN SUM(CASE WHEN SomeInt IS NULL THEN 1 ELSE 0 END) = COUNT(*)

THEN 'SomeInt contains only NULL values'

ELSE 'There are values in SomeInt column'

END

,CASE

WHEN SUM(CASE WHEN SomeDate IS NULL THEN 1 ELSE 0 END) = COUNT(*)

THEN 'SomeDate contains only NULL values'

ELSE 'There are values in SomeDate column'

END

FROM @t;

Greets

Flo

Ken McKelvey SSCoach Points: 18843 More actions · Answer 4

Or maybe some dynamic SQL:

DECLARE @sql varchar(MAX)

SET @sql = ''

SELECT @sql = @sql

+ 'SELECT ''' + table_schema + ''', '''

+ table_name + ''', '''

+ column_name+ ''', COUNT(' + column_name + ') '

+ 'FROM ' +table_schema + '.' + table_name + ' UNION ALL '

FROM information_schema.columns C

WHERE is_nullable = 'Yes'

SET @sql =

'SELECT ''ALTER TABLE '' + table_schema + ''.'' + table_name + '' DROP COLUMN '' + column_name + '' GO'' '

+ 'FROM ('

+ LEFT(@SQL, LEN(@SQL) - 10)

+ ') X (table_schema, table_name, column_name, total) WHERE total = 0 '

+ 'ORDER BY table_schema, table_name'

EXEC(@SQL)

el0 Valued Member Points: 71 More actions · Answer 5

I tried your dynamic code but sadly I could not get it to work...

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'DESC'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'ALL'.

Ken McKelvey SSCoach Points: 18843 More actions · Answer 6

Umm.... Works for me.

Copy the code exactly - spaces are important.

Also, there is no keyword DESC in my code.

el0 Valued Member Points: 71 More actions · Answer 7

Ken McKelvey (11/12/2009)
Umm.... Works for me.
Copy the code exactly - spaces are important.
Also, there is no keyword DESC in my code.

Yepp already done that but when quering the mssql server I get that error

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Try putting brackets about and of the places you concatenate the column name. It's possible you have column names with spaces in them, or reserved words.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?