Recently I came across a situation where the customer wanted to go through all
the custom fields that had been added since the system was first installed
years ago, and decide what was still relevant and what could be removed.
Fortunately they had upgraded to SQL Server, a while ago otherwise it would
have been a real pain in dBase to do the same thing.
I decided the best way to approach this was to get an output in the form of
FieldName Screen Name Blanks Sample Data
U24HRM 24 Hr Maintenance Man 34037 NO,NO,NO,NO,NO,NO,NO,NO,,NO,
U24HRP 24 Hour Security Person 34036 NO,NO,NO,NO,NO,NO,NO,NO,,NO,
So we could see what the field was called, how many records didn't have any
data, and what sort of data was actually in the fields.
The first step was to create a temporary table to hold the results.
Create table ibResultstemp(fld varchar(50),fldname varchar(100),blanks int, samples varchar(1000))
Next I created a small SQL procedure to find the field names from
information_schema.columns and then query each one in turn to find
out how many were blank, and also to find 10 records that were populated
as an example of the data.
declare @t varchar(100)
declare @sql varchar(1000)
declare @line varchar(1000)
declare c cursor for select column_name from information_schema.columns
where table_name = 'Contact2'
open c
fetch c into @t
while @@fetch_status=0
begin
set @sql='insert into ibresultstemp(fld,fldname,blanks,samples)
select '''+@t+''','''',count(*),'''' from contact2 where '+@t+' is null'
exec(@sql)
set @sql='update ibresultstemp set fldname=fielddesc from ibresultstemp
join
contudef on fld=field_name where dbfname=''Contact2'' and
field_name='''+@t+''''
exec(@sql)
set @sql='declare @line varchar(5000);'
set @sql=@sql+'set @line='''';'
set @sql=@sql +'select top 10 @line=cast('+@t+' as varchar)+'',''+@line
from contact2 where '+@t+' is not null;'
set @sql=@sql + 'update ibresultstemp set samples=left(@line,1000)
where fld='''+@t+''''
exec (@sql)
fetch next from c into @t
end
close c
deallocate c
select * from ibresultstemp
The results were then copied from the results grid into Excel and
presented to the customer.
This idea was to quickly get the Contact2 fields, but it could quickly
and easily be adapted for other tables.
|
