Post reply

Finding columns with null values

  • 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

  • April 26, 2021 at 8:41 pm

    #3876050

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • April 26, 2021 at 8:42 pm

    #3876051

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

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • April 26, 2021 at 8:43 pm

    #3876052

    it is a table and it does have null columns.

  • April 26, 2021 at 9:07 pm

    #3876058

    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)
    Intro to Tally Tables and Functions

  • April 26, 2021 at 9:10 pm

    #3876060

    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.

  • April 26, 2021 at 9:11 pm

    #3876062

    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)
    Intro to Tally Tables and Functions

  • April 26, 2021 at 9:13 pm

    #3876064

    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)
    Intro to Tally Tables and Functions

  • April 26, 2021 at 9:15 pm

    #3876065

    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.

  • April 26, 2021 at 9:22 pm

    #3876066

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

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • April 26, 2021 at 9:30 pm

    #3876067

    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)
    Intro to Tally Tables and Functions

  • April 26, 2021 at 9:31 pm

    #3876076

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • April 26, 2021 at 9:34 pm

    #3876077

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • April 26, 2021 at 9:42 pm

    #3876079

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • April 26, 2021 at 9:44 pm

    #3876080

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

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