sql script to get a column count for every column in a table

  • I'm looking for a script that will do a count for every column in a table including the number of null values

  • Is this what you require?

    CREATE TABLE #T(

    [NationalIDNumber] [nvarchar](15) NULL,

    [ContactID] [int] NULL,

    [ManagerID] [int] NULL)

    INSERT INTO #T

    SELECT 'GOT',NULL,NULL UNION ALL

    SELECT NULL,1,NULL UNION ALL

    SELECT NULL,NULL,2 UNION ALL

    SELECT NULL,NULL,NULL

    SELECT COUNT(*) AS 'All rows',COUNT(ContactID) as 'count of ContactID' FROM #T

    Results:

    All rows count of ContactID

    4 1

    DROP TABLE #T

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Well kinda. This table has 75 columns in it and I have to show two values for one column: 1) count where the column is not null, 2) where the column is null:

    example

    column count of values count of nulls

    column1 10 5

    column2 100 10

  • Getting close to midnight for me, and my brain is getting tired. But and it is a big but, you can retrieve all the column names for a table by

    using:

    SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'T' AND IS_Nullable = 'Yes'

    I created a table T with the same column definitions in my Temp DB and had returned

    NationalIDNumber

    ContactID

    ManagerID

    Now running out of brain power, sorry for that, next step is to save the column names, and use them to create a dynamic T-SQL statement.

    I know is is not enough, but hope it gets you started on a solution.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Still kinda vague what you're asking for. You mean that you want counts for EACH column in the table (and not "one column")? In your example, why do you show a total of 15 (ten not NULL and five NULL) for Column1 while the count for Column2 is 110? Wouldn't the totals always sum up to the number of records in the table (either they are NULL or they are not)?

    Column Name Count NULL Count Other

    ----------- ---------- -----------

    Column1 105 5

    Column2 95 15

    ...

    Column75 100 10

    Clarity will get you an answer faster...

  • ACinKC (10/26/2011)


    Still kinda vague what you're asking for. You mean that you want counts for EACH column in the table (and not "one column")? In your example, why do you show a total of 15 (ten not NULL and five NULL) for Column1 while the count for Column2 is 110? Wouldn't the totals always sum up to the number of records in the table (either they are NULL or they are not)?

    Column Name Count NULL Count Other

    ----------- ---------- -----------

    Column1 105 5

    Column2 95 15

    ...

    Column75 100 10

    Clarity will get you an answer faster...

    The other values in the table can be empty also (IS NULL)

    Regards,

    Sushant

    Regards
    Sushant Kumar
    MCTS,MCP

  • As promised on 10/24 finally found some free time. Now this code is not the fastest, and more than likely could be improved, but it does what I think you required. My table T would of course be your actual table with your data. I created my table (T) to test and illustrate the code.

    CREATE TABLE T([NationalIDNumber] [nvarchar](15) NULL,[ContactID] [int] NULL,[ManagerID] [int] NULL)

    INSERT INTO T

    SELECT 'xxx',NULL,NULL UNION ALL

    SELECT 'GOT',NULL,NULL UNION ALL

    SELECT NULL,1,NULL UNION ALL

    SELECT NULL,NULL,2 UNION ALL

    SELECT NULL,2,NULL UNION ALL

    SELECT NULL,1,NULL UNION ALL

    SELECT NULL,2,NULL UNION ALL

    SELECT NULL,NULL,NULL

    --to hold results

    CREATE TABLE Tempdb..R(Coln VARCHAR(20),Count_Other INT,Count_Null INT)

    --------Preperation complete

    -- Now develoe and execute the dynamic T-SQL statements

    SET NOCOUNT ON

    DECLARE @ColName VARCHAR(50)

    DECLARE @message VARCHAR(200)

    DECLARE @Sql NVARCHAR(200) --size may have to be increased

    DECLARE Col_cursor CURSOR FOR

    SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name = 'T'

    ORDER BY Column_Name

    OPEN Col_cursor

    FETCH NEXT FROM Col_cursor

    INTO @ColName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @Sql = 'SELECT '''

    SET @Sql = @Sql + @ColName +''',(COUNT(*)-(COUNT(*)-COUNT('+ @ColName + '))), + COUNT(*)-COUNT(' + @ColName + ') FROM T'

    --PRINT @Sql --Used for debugging

    INSERT INTO Tempdb..R

    EXECUTE sp_executesql @Sql

    FETCH NEXT FROM Col_cursor

    INTO @ColName

    END

    SELECT * FROM tempdb..R

    CLOSE col_cursor

    DEALLOCATE col_cursor

    SELECT * FROM T

    SELECT * FROM Tempdb..R

    --Cleanup for possible re-run

    -- DROP TABLE Tempdb..R

    Results:

    Col name Count_Other Count_Null

    ContactID 4 4

    ManagerID 1 7

    NationalIDNumber26

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks for all of your hard work

  • ericwenger1 (10/27/2011)


    Thanks for all of your hard work

    And thank you for replying, after my taking so long to respond I was afraid that you might miss the post. Hope it helped you and might assist others.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • how can you add a where to the above.

    So it only counts fields for value x in field a.

    select count(total) from tblt where tblt.name = 'name'

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

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