Analyze _each_field_

[MVisconte]

Folks,

I have a peculiar reqest from mgmt, namely, to come up w/ a "report" ("even if it's just an excel list") that will step through every field in every table in our Remedy DB to see which of the fields are not being used.

I know, strange, and ... a "bad" use of resources -- but I still have to get something done. It would be a run-once thing, and I am hoping someone can help me come up w/ a method.

I've got a stored procedure that will do a "foreachtable", and I can force it to just use the specific views we need... but I haven't come up w/ a good way to detect "not used" OR how to force that into a nested query... kind of like
sp_foreachtable do
for each column
select columnname, count(null) from column

I could really used suggestions both on how to get the above, as well as IS that the best (only) way to test for not used? What I'd like to wind up with would be checking for null, as well as "blank" or "empty" -- because there are no rules on what they put in these fields. MOST fields WILL be used, so I may wind up using any output as input to Crystal Reports, and pretty it up. I'd love to get an output like...

Group: Tablename, #_rows
Detail: columnname, #_null, #_blank_or_empty

and I am hoping that when processing in Crystal, I can use the #_null/ #_rows and #_blank_or_empty / #_rows to suppress any fields that look like they are actually being used.

Whew. Anybody? Any ideas?

Marc

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[JohnBates]

hi MVisconte,

I'll pass on a tip that sunila7 gave me yesterday.

run this in Query Analyzer:
select * from information_schema.columns where TABLE_CATALOG = 'name of your database'

This will give you all the attributes of each column in each user table. Data type, max length, whether it is nullable.

I selected INTO an output table that I can easily refresh and query into an Excel worksheet.
(I'm converting a 200 table db from Access to SQL 2000 so this is very handy to make sure I have all column name 'X' set to the same data type across all tables)

Don't know of a way to know if a column is used or WHEN it was last used.... you could do string searches thru all the stored procs I guess but how would you know that a col is not updated by a VB app ?


Maybe this will get you on your way.
good luck, John

 

[nigelrivett]

Something like this - hab=ven't tried to run it but should give you the idea.

create table #a (id int identity(1,1), tbl sysname, col sysname, type varchar(1), unused int)

insert #a (tbl, col) select c.TABLE_NAME, c.COLUMN_NAME , case when c.DATA_TYPE like '%char% then 'c' when c.DATA_TYPE like '%date% then 'd' else 'i' end from information_schema.columns c, information_schema.columns t where t.TABLE_NAME = c.TABLE_NAME and t.TABLE_TYPE = 'BASE TABLE'
declare @id int, @t sysname, @c sysname, @type varchar(1), @sql nvarchar(4000), @r int
select @id = 0
while @id < (select max(id) from #a
begin
select @id = @id + 1
select @t = tbl, @c = col, @type = type from #a where id = @id
select @sql = 'select @r = 1 where exists (select * from ' + @t + ' where ' + @c + case when @type = 'c' then ' <> '''' ' when @type = 'i' then ' <> 0 ' when @type = 'd' then ' <> ''19000101'' ' end
exec sp_executesql @sql, '@r int out', @r out
if @r = 1
update #a set unused = 1 where id =@id
end

select * from #a where unused = 1





======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[MVisconte]

If beer isn't cold, and it isn't fizzy, then what is it? (British...)

I think your response answers John Bate's purpose...

Strangely, The Powers That Be(TPTB)(tm) don't care what the field type, size, and constraints are, they just want to know if people are using them... specifically, how many are "null" or "blank".

It's not so much a database issue as it is a workflow issue, and I think that other methods to check/follow it should be used. I think that it's a bad use of SQL, Crystal (maybe), and personnel, but hey, I DO like paychecks, eh?

Found a couple of sp's to replace the sp_MSForEachTable and sp_MSForEachDB (basically, just more efficient). And, I've got a good sql that pulls every column from every table in every db into a results table. I mod'd it to pull just specific views from our Remedy db, but I haven't cobbled together a sub-query to pull null counts or blank counts.

Aside from running dedicated sql/sp's for EACH condition, IS there a (reasonably efficient) way to count nulls and blanks in one SQL?


--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports