Finding columns with null values

Question

Post reply

Finding columns with null values

astrid 69000

Hall of Fame

Points: 3208

April 26, 2021 at 7:25 pm

#3876013

Hi,

I have been using a code that someone else wrote to find which columns have null values. Since I have updated my management studio to 18, it is not longer working, not sure why.

Instead of getting the columns I get

Commands completed successfully.

Completion time: 2021-04-26T15:24:39.7570119-04:00

Here is the code.

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'OPERATION'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM NAP.OPERATION WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
    EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

Thanks

Astrid

Viewing 15 posts - 1 through 15 (of 24 total)

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 1

Did you run the SELECT by itself to make sure you get results from that?

SELECT c.name 
FROM sys.tables t 
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'OPERATION'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

Is it possible that OPERATION is a view (or synonym) and not a table?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 3

astrid 69000

Hall of Fame

Points: 3208

April 26, 2021 at 8:43 pm

#3876052

it is a table and it does have null columns.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

astrid 69000 wrote:

it is a table and it does have null columns.

You code works correctly when I changed that table name to a test table I made specifically for the purpose.

Make the dynamic SQL materialize and run that. I'm thinking that your table doesn't have any column that is 100% filled with NULLs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 5

Hi,

I get the object id, it stopped working when I updated the management studio, i think it is not the code, but rather a new thing i need to enable.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

astrid 69000 wrote:

Hi,
I get the object id, it stopped working when I updated the management studio, i think it is not the code, but rather a new thing i need to enable.

Make the dynamic SQL materialize and run that. I'm thinking that your table doesn't have any column that is 100% filled with NULLs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

And let's hope it's not an update to SSMS that's causing the problem. Which edition and version of SQL Server do you have and to which version of SSMS did you upgrade to?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 8

i upgraded to 18.8, but i will check, thanks to all of you. i think it is a problem on my side, i know the code was working.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 9

Btw, why are you using such an inefficient method, checking the column values one by one?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 10

ScottPletcher wrote:

Btw, why are you using such an inefficient method, checking the column values one by one?

What are you asking why instead of posting a link to something better? 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101095 More actions · Answer 11

Jeff Moden wrote:

astrid 69000 wrote:
Hi,
I get the object id, it stopped working when I updated the management studio, i think it is not the code, but rather a new thing i need to enable.
Make the dynamic SQL materialize and run that. I'm thinking that your table doesn't have any column that is 100% filled with NULLs.

OP stated that at least one column does contain a NULL value, so, yeah, it's odd.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101095 More actions · Answer 12

Jeff Moden wrote:

ScottPletcher wrote:
Btw, why are you using such an inefficient method, checking the column values one by one?
What are you asking why instead of posting a link to something better? 😀

Then why are you asking me that instead of posting something better yourself? 😀

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101095 More actions · Answer 13

I asked because many people are happy with code as long as it ultimately does what they need it to do, i.e. "the code's good enough for me." In that case, it's a waste of my time to code an improved method. I don't have an existing link to reference (all code I've seen on the internet is just as inefficient, it really seems they all copied from the same original method), so I'd have to code it myself (or modify some code I've done for work here). I've got tons of stuff to do right now, so if OP is good with the method they have, I'm not gonna post code they are very likely to ignore anyway.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101095 More actions · Answer 14

This query or one just like it seems to come up a lot. But I still don't know if there's enough demand for, say, a full article on it. It's still a rather niche topic.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".