Table fillrate and other info!

Question

Post reply

Table fillrate and other info!

super101

SSC Veteran

Points: 266
More actions
March 30, 2011 at 2:39 am

#237561

Hi Guys,
Been looking for a really simple udf/proc..
I want to be able to quickly gather:
- the min/max values,
- percentage filled (ie. 1 record out of 10 rows is 10% filled etc)
- Total count,
- Total unique
and anything else you would deem relevant for any table with any number of columns.
I would like the results to be shown with the columns going down the left and across the right the above headers. So all the info is captured for each field.
Iv been looking for this everywhere as its very useful to see an overview of a table of data and its an easy output for a client.
Please help!
Thanks
Andy

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Look up MIN, MAX, COUNT functions. They'll do 3 of the 4.

What do you mean by % filled?

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

super101 SSC Veteran Points: 266 More actions · Answer 2

Hi,

Thanks for the quick response!

% filled i mean -

say theres a 1000 rows and theres 500 records populated and 500 not, the percentage filled would be 50%.

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Still don't understand. If there are 1000 rows how can only 500 be populated?

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

super101 SSC Veteran Points: 266 More actions · Answer 4

Its address data mainly i deal with....

so theres alot of nulls all over the show across all the columns(except the primary key say).

Does this make sense?

The maths would be total records that is not null DIVIDED BY total records * 100.. im just having trouble udf'ing it up for all fields...

So some only had 1 address line and some have 5. Often the data I get is brocken and requires mass cleaning / juggling around. and i need to show the client the difference of the structure of the table % filled before and after cleansing.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

You're not going to be able to do a udf if you want one query that can plug any table name and any column in. That will require dynamic SQL, and that can't be done in a udf.

Maybe show the table structure you have, some sample data and expected results?

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

super101 SSC Veteran Points: 266 More actions · Answer 6

Ideal Output would be for below sample:

COLUMN NAMETotal_RecordsTotal_Distinct_Records% PopulatedMax ValueMin ValueField Type

AccountID3030100%12341INT

CompanyName3025100%etcetcvarchar

Address1302066%etcetcchar

Address2272066%etcetcnvarchar

Address3315etcetcetc

Town City14125etcetcetc

County17115etcetcetc

Postcode30305etcetcetc

Postal Area30305etcetcetc

ContactID30305etcetcetc

Contact_DateLastModified30305etcetcetc

Title2645etcetcetc

FirstName2295etcetcetc

LastName30305etcetcetc

JobTitle22225etcetcetc

JobFunction22225etcetcetc

JobLevel22225etcetcetc

isPersonal22225etcetcetc

ContactStatus2535etcetcetc

Turnover225etcetcetc

Num Employees At Site14145etcetcetc

Num Employees In Group14145etcetcetc

UKSIC20031455etcetcetc

LocationType14145etcetcetc

Typical sample data could be(hope this works):"AccountID","CompanyName","Address1","Address2","Address3","Town City","County","Postcode","Postal Area","ContactID","Contact_DateLastModified","Title","FirstName","LastName","JobTitle","JobFunction","JobLevel","isPersonal","ContactStatus","Turnover","Num Employees At Site","Num Employees In Group","UKSIC2003","LocationType"

"928","First ScotRail ltd","Atrium Court","50 Waterloo Street","","Glasgow","Lanarkshire","G2 6HQ","G","13705","2010-08-02 15:54:22","Mr","Jack","Goodall","Purchasing Manager","Procurement","Manager","True","Confirmed","","","4500","60100",""

"928","First ScotRail ltd","Atrium Court","50 Waterloo Street","","Glasgow","Lanarkshire","G2 6HQ","G","13706","2010-08-02 15:54:22","Mr","Graeme","MacFarlen","Marketing Director","Marketing","Director","True","Confirmed","","","4500","60100",""

"1210","Pinsent Masons LLP","3 Colmore Circus","","","Birmingham","West Midlands","B4 6BH","B","15144","2010-09-02 14:00:58","","Kate","Rees","Procurement Director","Procurement","Director","False","Confirmed","","","2000","",""

"1210","Pinsent Masons LLP","3 Colmore Circus","","","Birmingham","West Midlands","B4 6BH","B","15145","2010-09-02 14:01:02","Mr","Colin","Smith","IT Director","Information Technology","Director","False","Confirmed","","","2000","",""

"1210","Pinsent Masons LLP","3 Colmore Circus","","","Birmingham","West Midlands","B4 6BH","B","15146","2010-09-02 14:01:01","Ms","Johanna","Tross","Human Resources Director","Human Resources","Director","False","Confirmed","","","2000","",""

"1211","Pitney Bowes Limited","The Pinnacles","Elizabeth Way","","Harlow","Essex","CM19 5BD","CM","15147","2010-08-04 11:57:55","Mr","David","Jefferies","Marketing Director","Marketing","Director","True","Confirmed","","","1500","30010",""

"1211","Pitney Bowes Limited","The Pinnacles","Elizabeth Way","","Harlow","Essex","CM19 5BD","CM","15148","2010-08-04 11:58:56","Mr","Patrick","Keddy","Chief Executive Officer","","CEO","True","Confirmed","","","1500","30010",""

"1211","Pitney Bowes Limited","The Pinnacles","Elizabeth Way","","Harlow","Essex","CM19 5BD","CM","15149","2010-08-04 11:57:57","Mr","David","Marfleet","Finance Director","Finance","Director","True","Confirmed","","","1500","30010",""

"1211","Pitney Bowes Limited","The Pinnacles","Elizabeth Way","","Harlow","Essex","CM19 5BD","CM","15150","2010-08-04 11:57:54","Mr","Dave","Strain","IT Manager","Information Technology","Director","True","Confirmed","","","1500","30010",""

"1225","PZ CUSSONS INTERNATIONAL LTD","Aviator Way","Manchester Business Park","","Manchester","Lancashire","M22 5TG","M","15211","2011-01-24 14:30:15","Mr","Neil","Cragie","Sales & Marketing Director","Marketing","Director","True","Confirmed","","","360","51150",""

"700","20:20 Mobile Group Limited","Weston Road","","","Crewe","Cheshire","CW1 6BU","CW","12462","2011-02-11 09:22:26","Ms","Joyce","Mercer","HR Director","Human Resources","Manager","True","Confirmed","","","350","74150",""

"700","20:20 Mobile Group Limited","Weston Road","","","Crewe","Cheshire","CW1 6BU","CW","12465","2011-02-11 09:21:49","Mr","Andrew","Pete","Marketing Director","Marketing","Director","True","Confirmed","","","350","74150",""

"700","20:20 Mobile Group Limited","Weston Road","","","Crewe","Cheshire","CW1 6BU","CW","12466","2011-02-11 09:22:25","Mr","Steve","Sangara","Sales Director","Sales","Director","True","Confirmed","","","350","74150",""

"700","20:20 Mobile Group Limited","Weston Road","","","Crewe","Cheshire","CW1 6BU","CW","77566","2011-02-11 09:23:14","","Mark","Hardy","Procurement Manager","Procurement","Manager","True","Confirmed","","","350","74150",""

"7854","SEPOC HOLDINGS LTD","Angel Road Wks Advent Way","Edmonton","","London","","N18 3AH","N","64385","2010-09-29 09:10:51","Ms","Linda","Glatzel","Director of IT","Information Technology","","False","","","","","",""

"701","A.F. Blakemore And Son Ltd","Long Acre Industrial Estate","Rosehill","","Willenhall","West Midlands","WV13 2JP","WV","12468","2011-01-14 10:30:24","Mr","Steve","Cloves","Human Resources Director","Human Resources","Director","True","Confirmed","710000","","20","51390",""

"701","A.F. Blakemore And Son Ltd","Long Acre Industrial Estate","Rosehill","","Willenhall","West Midlands","WV13 2JP","WV","12469","2011-01-14 10:29:59","Mr","Lee","Coleysure","Finance Director","Finance","Director","True","Confirmed","","","20","51390",""

"701","A.F. Blakemore And Son Ltd","Long Acre Industrial Estate","Rosehill","","Willenhall","West Midlands","WV13 2JP","WV","12470","2011-01-14 10:27:48","Mr","Denis","Evens","Managing Director","","Managing Director","True","Confirmed","","","20","51390",""

"701","A.F. Blakemore And Son Ltd","Long Acre Industrial Estate","Rosehill","","Willenhall","West Midlands","WV13 2JP","WV","12471","2011-01-14 10:28:53","Mr","James","Lloyd","IT Director","Information Technology","Director","True","Confirmed","710000","","20","51390",""

"701","A.F. Blakemore And Son Ltd","Long Acre Industrial Estate","Rosehill","","Willenhall","West Midlands","WV13 2JP","WV","16889","2011-01-14 10:27:16","Mr","Ged","Carter","Marketing Director","Marketing","Director","True","Left","","","20","51390",""

"715","Agilisys","2nd Floor","26-28 Hammersmith Grove","","London","","W6 7AW","W","12562","2011-01-19 17:20:22","Mr","Richard","Amos","Finance Director","Finance","Director","False","Left","9193000","","393","72210",""

"715","Agilisys","2nd Floor","26-28 Hammersmith Grove","","London","","W6 7AW","W","12563","2011-01-19 17:18:16","Mr","Darren","Elliston","IT Director","Information Technology","Director","True","Confirmed","9193000","","393","72210",""

"1225","PZ CUSSONS INTERNATIONAL LTD","Aviator Way","Manchester Business Park","","Manchester","Lancashire","M22 5TG","M","15212","2011-01-24 14:29:36","Mr","Alex","Kanellis","Chief Executive Officer","","CEO","True","Confirmed","","","360","51150",""

"1225","PZ CUSSONS INTERNATIONAL LTD","Aviator Way","Manchester Business Park","","Manchester","Lancashire","M22 5TG","M","15213","2011-01-24 14:29:58","Mr","Brandon","Leigh","Finance Director","Finance","Director","True","Confirmed","","","360","51150",""

"1225","PZ CUSSONS INTERNATIONAL LTD","Aviator Way","Manchester Business Park","","Manchester","Lancashire","M22 5TG","M","15214","2011-01-24 14:30:22","Mr","Richard","Steward","IT Director","Information Technology","Director","True","Confirmed","","","360","51150",""

"1226","Quadrant Catering Limited","Quadrant House","2 Drakes Meadow","","Swindon","Wiltshire","SN3 3LL","SN","15215","2010-08-04 15:03:02","Mr","Steve","Buesden","Managing Director","","Managing Director","False","Confirmed","","","900","55520",""

"1226","Quadrant Catering Limited","Quadrant House","2 Drakes Meadow","","Swindon","Wiltshire","SN3 3LL","SN","15216","2010-09-02 14:36:51","Mr","Matthew","Garnham","Network Specialist","Information Technology","Director","False","Confirmed","","","900","55520",""

"1227","QUADRIGA WORLDWIDE LTD","Forum One","Station Road","","Theale","Berkshire","RG7 4RA","RG","15217","2011-01-24 14:35:32","","Louise","Clarke","Human Resources Director","Human Resources","Director","False","Left","","","90","52630",""

"1227","QUADRIGA WORLDWIDE LTD","Forum One","Station Road","","Theale","Berkshire","RG7 4RA","RG","15218","2010-08-04 15:04:21","Mr","Adam","Ferriday","IT Director","Information Technology","Director","False","Confirmed","","","90","52630",""

"1227","QUADRIGA WORLDWIDE LTD","Forum One","Station Road","","Theale","Berkshire","RG7 4RA","RG","15219","2011-01-24 14:39:43","Mr","Gary","Marsh","Operations Director","Operations","Director","False","Confirmed","","","90","52630",""

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Table definition?

Please read through this: http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

super101 SSC Veteran Points: 266 More actions · Answer 8

sorry better view of sample outpout for random table:

[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Still need a table definition (create 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

super101 SSC Veteran Points: 266 More actions · Answer 10

Sorry took me a while!

see below:

CREATE TABLE #mytable

(

AccountID NVARCHAR(255) NULL ,

CompanyName NVARCHAR(255) NULL ,

Address1 NVARCHAR(255) NULL ,

Address2 NVARCHAR(255) NULL ,

Address3 NVARCHAR(255) NULL ,

[Town City] NVARCHAR(255) NULL ,

County NVARCHAR(255) NULL ,

Postcode NVARCHAR(255) NULL ,

[Postal Area] NVARCHAR(255) NULL ,

ContactID NVARCHAR(255) NULL ,

Contact_DateLastModified NVARCHAR(255) NULL ,

Title NVARCHAR(255) NULL ,

FirstName NVARCHAR(255) NULL ,

LastName NVARCHAR(255) NULL ,

JobTitle NVARCHAR(255) NULL ,

JobFunction NVARCHAR(255) NULL ,

JobLevel NVARCHAR(255) NULL ,

isPersonal NVARCHAR(255) NULL ,

ContactStatus NVARCHAR(255) NULL

)

INSERT INTO #mytable

(AccountID,CompanyName,Address1,Address2,Address3,[Town City],County,Postcode,[Postal Area],ContactID,Title,FirstName,LastName,JobTitle,JobFunction,JobLevel,ContactStatus)

SELECT '928','First ScotRail ltd','Atrium Court','50 Waterloo Street','NULL','Glasgow','Lanarkshire','G2 6HQ','G','13705','Mr','Jack','Goodall','Purchasing Manager','Procurement','Manager','Confirmed'

UNION ALL

SELECT '928','First ScotRail ltd','Atrium Court','50 Waterloo Street','NULL','Glasgow','Lanarkshire','G2 6HQ','G','13706','Mr','Graeme','MacFarlen','Marketing Director','Marketing','Director','Confirmed'

UNION ALL

SELECT '1210','Pinsent Masons LLP','3 Colmore Circus','NULL','NULL','Birmingham','West Midlands','B4 6BH','B','15144','NULL','Kate','Rees','Procurement Director','Procurement','Director','Confirmed'

UNION ALL

SELECT '1210','Pinsent Masons LLP','3 Colmore Circus','NULL','NULL','Birmingham','West Midlands','B4 6BH','B','15145','Mr','Colin','Smith','IT Director','Information Technology','Director','Confirmed'

UNION ALL

SELECT '1210','Pinsent Masons LLP','3 Colmore Circus','NULL','NULL','Birmingham','West Midlands','B4 6BH','B','15146','Ms','Johanna','Tross','Human Resources Director','Human Resources','Director','Confirmed'

UNION ALL

SELECT '1211','Pitney Bowes Limited','The Pinnacles','Elizabeth Way','NULL','Harlow','Essex','CM19 5BD','CM','15147','Mr','David','Jefferies','Marketing Director','Marketing','Director','Confirmed'

UNION ALL

SELECT '1211','Pitney Bowes Limited','The Pinnacles','Elizabeth Way','NULL','Harlow','Essex','CM19 5BD','CM','15148','Mr','Patrick','Keddy','Chief Executive Officer','NULL','CEO','Confirmed'

UNION ALL

SELECT '1211','Pitney Bowes Limited','The Pinnacles','Elizabeth Way','NULL','Harlow','Essex','CM19 5BD','CM','15149','Mr','David','Marfleet','Finance Director','Finance','Director','Confirmed'

UNION ALL

SELECT '1211','Pitney Bowes Limited','The Pinnacles','Elizabeth Way','NULL','Harlow','Essex','CM19 5BD','CM','15150','Mr','Dave','Strain','IT Manager','Information Technology','Director','Confirmed'

UNION ALL

SELECT '1225','PZ CUSSONS INTERNATIONAL LTD','Aviator Way','Manchester Business Park','NULL','Manchester','Lancashire','M22 5TG','M','15211','Mr','Neil','Cragie','Sales & Marketing Director','Marketing','Director','Confirmed'

UNION ALL

SELECT '700','20:20 Mobile Group Limited','Weston Road','NULL','NULL','Crewe','Cheshire','CW1 6BU','CW','12462','Ms','Joyce','Mercer','HR Director','Human Resources','Manager','Confirmed'

UNION ALL

SELECT '700','20:20 Mobile Group Limited','Weston Road','NULL','NULL','Crewe','Cheshire','CW1 6BU','CW','12465','Mr','Andrew','Pete','Marketing Director','Marketing','Director','Confirmed'

UNION ALL

SELECT '700','20:20 Mobile Group Limited','Weston Road','NULL','NULL','Crewe','Cheshire','CW1 6BU','CW','12466','Mr','Steve','Sangara','Sales Director','Sales','Director','Confirmed'

UNION ALL

SELECT '700','20:20 Mobile Group Limited','Weston Road','NULL','NULL','Crewe','Cheshire','CW1 6BU','CW','77566','NULL','Mark','Hardy','Procurement Manager','Procurement','Manager','Confirmed'

SELECT * FROM #mytable

super101 SSC Veteran Points: 266 More actions · Answer 11

CREATE TABLE #mytable

(

AccountID NVARCHAR(255) NULL ,

CompanyName NVARCHAR(255) NULL ,

Address1 NVARCHAR(255) NULL ,

Address2 NVARCHAR(255) NULL ,

Address3 NVARCHAR(255) NULL ,

[Town City] NVARCHAR(255) NULL ,

County NVARCHAR(255) NULL ,

Postcode NVARCHAR(255) NULL ,

[Postal Area] NVARCHAR(255) NULL ,

ContactID NVARCHAR(255) NULL ,

Contact_DateLastModified NVARCHAR(255) NULL ,

Title NVARCHAR(255) NULL ,

FirstName NVARCHAR(255) NULL ,

LastName NVARCHAR(255) NULL ,

JobTitle NVARCHAR(255) NULL ,

JobFunction NVARCHAR(255) NULL ,

JobLevel NVARCHAR(255) NULL ,

isPersonal NVARCHAR(255) NULL ,

ContactStatus NVARCHAR(255) NULL

)

INSERT INTO #mytable

(AccountID,CompanyName,Address1,Address2,Address3,[Town City],County,Postcode,[Postal Area],ContactID,Title,FirstName,LastName,JobTitle,JobFunction,JobLevel,ContactStatus)

SELECT '928','First ScotRail ltd','Atrium Court','50 Waterloo Street','NULL','Glasgow','Lanarkshire','G2 6HQ','G','13705','Mr','Jack','Goodall','Purchasing Manager','Procurement','Manager','Confirmed'

UNION ALL

SELECT '928','First ScotRail ltd','Atrium Court','50 Waterloo Street','NULL','Glasgow','Lanarkshire','G2 6HQ','G','13706','Mr','Graeme','MacFarlen','Marketing Director','Marketing','Director','Confirmed'

UNION ALL

SELECT '1210','Pinsent Masons LLP','3 Colmore Circus','NULL','NULL','Birmingham','West Midlands','B4 6BH','B','15144','NULL','Kate','Rees','Procurement Director','Procurement','Director','Confirmed'

UNION ALL

SELECT '1210','Pinsent Masons LLP','3 Colmore Circus','NULL','NULL','Birmingham','West Midlands','B4 6BH','B','15145','Mr','Colin','Smith','IT Director','Information Technology','Director','Confirmed'

UNION ALL

SELECT '1210','Pinsent Masons LLP','3 Colmore Circus','NULL','NULL','Birmingham','West Midlands','B4 6BH','B','15146','Ms','Johanna','Tross','Human Resources Director','Human Resources','Director','Confirmed'

UNION ALL

SELECT '1211','Pitney Bowes Limited','The Pinnacles','Elizabeth Way','NULL','Harlow','Essex','CM19 5BD','CM','15147','Mr','David','Jefferies','Marketing Director','Marketing','Director','Confirmed'

UNION ALL

SELECT '1211','Pitney Bowes Limited','The Pinnacles','Elizabeth Way','NULL','Harlow','Essex','CM19 5BD','CM','15148','Mr','Patrick','Keddy','Chief Executive Officer','NULL','CEO','Confirmed'

UNION ALL

SELECT '1211','Pitney Bowes Limited','The Pinnacles','Elizabeth Way','NULL','Harlow','Essex','CM19 5BD','CM','15149','Mr','David','Marfleet','Finance Director','Finance','Director','Confirmed'

UNION ALL

SELECT '1211','Pitney Bowes Limited','The Pinnacles','Elizabeth Way','NULL','Harlow','Essex','CM19 5BD','CM','15150','Mr','Dave','Strain','IT Manager','Information Technology','Director','Confirmed'

UNION ALL

SELECT '1225','PZ CUSSONS INTERNATIONAL LTD','Aviator Way','Manchester Business Park','NULL','Manchester','Lancashire','M22 5TG','M','15211','Mr','Neil','Cragie','Sales & Marketing Director','Marketing','Director','Confirmed'

UNION ALL

SELECT '700','20:20 Mobile Group Limited','Weston Road','NULL','NULL','Crewe','Cheshire','CW1 6BU','CW','12462','Ms','Joyce','Mercer','HR Director','Human Resources','Manager','Confirmed'

UNION ALL

SELECT '700','20:20 Mobile Group Limited','Weston Road','NULL','NULL','Crewe','Cheshire','CW1 6BU','CW','12465','Mr','Andrew','Pete','Marketing Director','Marketing','Director','Confirmed'

UNION ALL

SELECT '700','20:20 Mobile Group Limited','Weston Road','NULL','NULL','Crewe','Cheshire','CW1 6BU','CW','12466','Mr','Steve','Sangara','Sales Director','Sales','Director','Confirmed'

UNION ALL

SELECT '700','20:20 Mobile Group Limited','Weston Road','NULL','NULL','Crewe','Cheshire','CW1 6BU','CW','77566','NULL','Mark','Hardy','Procurement Manager','Procurement','Manager','Confirmed'

SELECT * FROM #mytable

super101 SSC Veteran Points: 266 More actions · Answer 12

super101

SSC Veteran

Points: 266

March 31, 2011 at 4:53 am

#1305893

Can anyone help with this???

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

I'm working on it, but it's not simple and I can't work on it while busy with client work.

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

super101 SSC Veteran Points: 266 More actions · Answer 14

super101

SSC Veteran

Points: 266

March 31, 2011 at 5:04 am

#1305896

Thankyou very much didnt realise!