Determining column / field usage....

Question

Determining column / field usage....

darth.pathos

Ten Centuries

Points: 1084
More actions
January 7, 2010 at 9:00 am

#96372

Hi all...
Is there a way to do a quick check of how often the columns in a group of tables are being used? I have a sneaking suspicion that some columns that were argued for, and which we've paid good money for, are not being used. Yes, i could go select count(*) from table_name where column_name is null, but I'd have to do that for each column in each table. There's about 200 columns in 25 tables that I need to check, so this would be a very cumbersome way of going about it. I have played around with the syscolumns and systables, but can't figure out how to join that information back to my data so that I can have something along the lines of
select sc.name, count(*)
from syscolumns sc, table_name tn
where sc.name = ??????
group by sc.name
order by sc.name
Any ideas?? Thanks in advance.....
Chris

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

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

auaiomrn Ten Centuries Points: 1300 More actions · Answer 1

I don't think you'd be able to do it with one query... you'd have to make a cursor that goes through each column of each table and runs the select.

You can use INFORMATION_SCHEMA.COLUMNS for this.

darth.pathos Ten Centuries Points: 1084 More actions · Answer 2

...alrightie - thanks for the reply auaiomrn - i've gotten as far as finding the Information_Schema.columns view...

I know what a cursor is, but I've never used or created one. I've looked in BOL and will be doing more research in the forums and online, but wonder if someone could provide me an example of how I would set this up.

Thanks in advance

Chris

Edit: OK, I have built my first cursor (yay) and have it running through the tables, extracting the Table name and Column name. Soooo now how do I go about adding the counts?? I can't think of how to join the Row information with the Column name and do a count. Completely baffled, but maybe it's just me staring at my computer screen too much....

TIA

Chris

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

MANU-J.

SSC-Dedicated

Points: 31126

January 12, 2010 at 2:35 pm

#1102768

Were you able to resolve it yesterday?

MJ

darth.pathos Ten Centuries Points: 1084 More actions · Answer 4

Hi Manu-J,

No, I wasn't able to; I didn't spend too much more time after I posted my edit as I had a lot of other stuff on the go and couldn't afford the time. I'll be continuing to work on it tomorrow, but if you have suggestions I'd really appreciate it.

Thanks

Chris

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

What is your criteria for determining if a column is used or not within a table? If it is null?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

This may be suitable for your needs.

Select 'Select count(*) From ' + table_name + ' where [' + column_name + '] is null;' from information_schema.columns

Select 'Select count(*) From ' + table_name + ' where [' + column_name + '] is not null;' from information_schema.columns

I even looked into doing the following

Select 'Select count([' + column_name + ']) From ' + table_name + char(13) +' go ' from information_schema.columns

However, it does not like text columns. The query could be parred down to eliminate any columns of text type.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

darth.pathos Ten Centuries Points: 1084 More actions · Answer 7

Hi CirqueduSQLeil,

Your post was exactly what I needed to get me started. I took the code from your post and modified it to this:

Select table_name+'_'+column_name, 'insert into #Temp Select

count(case when ('+column_name+' is not null)then 1 end) as NNull,

count(case when ('+column_name+' is null)then 1 end) as YNull

From '

+ table_name +';'

from information_schema.columns

I then ran the SQL that was generated, as well as a Select * from #Temp. The select * gave me the Table Name and Column Name, and the generated SQL gave me the counts. I assumed that because I was doing things in a specific order, the order of the Table+Column Names would be the same order that the counts were generated in. I did a check on 15 of the tables and everything was OK, so I'm happy.

I'm sure there are a dozen more efficient ways of doing this, but for now I'm happy with this.

Thanks again

Chris

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Most excellent. Glad to be of service.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events