how to know counts of field usage in a table

Question

how to know counts of field usage in a table

1980gamer

SSC Enthusiast

Points: 139
More actions
January 12, 2007 at 2:01 pm

#171041

Hi all,
i have been searching everywhere to find a query that will show me how often a field in a given table is used.
So, if table x has 5 fields.. a,b,c,d,e and a is ID not nullable..
I want to have something like:
Total records =12345 a=12345 b=2323 c=12001 d=123 e=2
So, i could consider removing column e... only 2 values entered for 12345 records.
Maybe my searching has been bad? field counts per table and variation of that.

Please help! Thanks in advance!
gene

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

You could test how many fields are empty, ie null/not null, but not how of often they are used.

when SQL inserts or updates a row, no statistics are available wether the insert statement affected just specific columns.

an example for null counts:

SELECT CASE WHEN COLB IS NULL THEN 0 ELSE 1 END AS COLB_Counts,

CASE WHEN COLC IS NULL THEN 0 ELSE 1 END AS COLC_Counts,

CASE WHEN COLD IS NULL THEN 0 ELSE 1 END AS COLD_Counts,

CASE WHEN COLE IS NULL THEN 0 ELSE 1 END AS COLE_Counts

FROM SOMETABLE

the lower the number, the fewer values exist in the data.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

1980gamer SSC Enthusiast Points: 139 More actions · Answer 2

Thanks Lowell,

I was trying to automate this too.

I have many denormalized tables I am trying to shrink...

250 to 325 fields.. not pretty! I was hoping to use variables to read the table structure and query each field! I do not do to much in transact.. but I guess I need to start!

Thanks for the help.

Gene

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Can you just do:

Select Count(fieldname) from myTable?

select

count(*), count(addressline2) from Person.Address

returns from AdventureWorks database:

19614 362

Count(*) returns the number of all rows, and count(fieldname) returns only the number of non-null rows.

You can automate selecting column names by doing:

select

o.name, c.name from sys.all_columns c

join

sys.all_objects o

on

o.object_id = c.object_id

where

type ='u'

Regards,Yelena Varsha

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

I'm not sure about automating this, would probably be fairly ugly sql. I usually divide the number of rows in the table by the distinct values in a row to get selectivity, then if this is poor I investigate further ( where 1 is good and the higher the value the worse the selectivity )

In my extreme view any nullable column is considered bad!!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/