November 17, 2009 at 11:26 am
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
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
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
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
November 17, 2009 at 1:55 pm
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy