Post reply

Distinct Column and NULL Counts for Metadata Extract

  • November 17, 2009 at 11:26 am

    #212680

    Hi,

    Is there a system sproc that captures distinct column and NULL counts in sql server? I am capturing column stats for a metadata project. Looking at information schema the level of detail I am looking for is not available.

    Thanks,

    costa

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • November 17, 2009 at 11:39 am

    #1080428

    Are you trying to figure out how many rows of each column are null, vs how many have each distinct value?

    You won't be able to get that from metadata. You could get it from a dynamic SQL query that would give you the results for each column in the database.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 17, 2009 at 12:24 pm

    #1080458

    That is correct but I am trying to advoid using distinct count query. My hope is that sql server has it in system table / views that I can just query. Any custom solution poses potential performance issues we dont want to deal with in the future.

    - costa

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • November 17, 2009 at 12:49 pm

    #1080486

    cos_ta393 (11/17/2009)

    My hope is that sql server has it in system table / views that I can just query.

    It doesn't. If you want to know how many rows have a particular column null, you have to query the table. For the distinct values, if there are statistics on that column and it's not a problem if the data may be out of date or plain inaccurate, you can try and use the output of DBCC SHOW_STATISTICS. I would still say the best way is to query the table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 17, 2009 at 1:55 pm

    #1080527

    Thanks Gail and GSquared. You have answered my question. Your help is appreciated.

    costa

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • November 18, 2009 at 6:53 am

    #1080858

    You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 6 posts - 1 through 5 (of 5 total)

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