Frequency of each field in table
This SP will generate the frequency of each occurrence in a field or for every field in a table. The syntax is
exex sp_freqall , , , ,
for example:
exec sp_freqall utems2000_2001, null,lastname, 100
would return a table (with a field name and a count) for each field in the utems2000_2001 table, except the lastname field and any field with more than 100 unique occurrences of a value.
/****** Object: Stored Procedure dbo.sp_FreqAll Script Date: 3/27/00 4:10:41 PM ******//****** Object: Stored Procedure dbo.sp_FreqAll Script Date: 11/9/99 3:57:20 PM ******/CREATE PROCEDURE sp_FreqAll (@Table varchar(60)=''
,@sInclude varchar(8000)=''
,@sExclude varchar(8000)=''
,@iCutOff int=0
,@sWhere varchar(8000)=''
) AS
set nocount on
declare @sName sysname
, @sField varchar(40)
, @iId int
, @sType varchar(2)
, @iType tinyint
, @sMsg varchar(8000)
, @sSQL varchar(8000)
, @nInx int
, @bRestrict tinyint
, @iCount int
, @sDBName varchar(60)
, @sOwner varchar(60)
, @sTableName varchar(60)
, @iDelim tinyint
, @sChunk varchar(60)
, @bInclude int
, @bIncluded int
, @bExclude int
, @bExcluded int
, @bCutoff int
, @sClause varchar(8000)
, @sCross varchar(40)
, @bWhere int
, @bCross bit
if @Table = ''
print 'execute sp_FreqAll <Table Name>, <Inclusion list>,<Exclusion List>, <Cutoff Count>'
else
begin
/* Parse the Tablename */ /* print 'Parse the TableName' */ select @iDelim = charindex('.',@Table)
if @iDelim > 0
begin
select @sChunk = substring(@Table,1,@iDelim - 1)
select @Table = substring(@Table,@iDelim + 1,50)
select @iDelim = charindex('.',@Table)
if @iDelim > 0
begin
select @sDBName = @sChunk
select @sOwner = rtrim(substring(@Table,1,@iDelim - 1))
select @sTableName = substring(@Table,@iDelim + 1,50)
end
else
begin
select @sDBName = db_name()
select @sOwner = rtrim(@sChunk)
select @sTableName = @Table
end
end
else
begin
select @sDBName = db_name()
select @sOwner = user
select @sTableName = @Table
end
/*
* print @sDBName
* print @sOwner
* print @sTableName
*/ if @iCutOff = 0
begin
select @bCutoff = 0
select @iCutOff = 2000000
end
else
begin
select @bCutoff = 1
end
declare Cur_Table Cursor
for Select Name, Id, Type
from sysobjects
where name = @sTableName
and uid = user_id(@sOwner)
if datalength(@sWhere) > 0
select @bWhere = 1
else
select @bWhere = 0
if charindex('*',@sInclude) > 0
begin
select @bCross = 1
select @sCross = substring(@sInclude,1,charindex('*',@sInclude) - 1)
select @sInclude=substring(@sInclude,charindex('*',@sInclude)+1,255)
end
else
select @bCross = 0
open Cur_Table
fetch Cur_Table into @sName, @iId, @sType
close Cur_Table
deallocate Cur_Table
if @sType <> 'U' and @sType <> 'V'
begin
select @sMsg = 'Must be a table ('
+ @sDBName + '.' + @sOwner + '.' + @sTableName + ')'
print @sMsg
end
else
begin
if datalength(rtrim(@sExclude)) > 0
select @bExclude = 1
else
select @bExclude = 0
if datalength(rtrim(@sInclude)) > 0
select @bInclude = 1
else
select @bInclude = 0
declare Cur_Fields Cursor for
Select Name, Type
from syscolumns
where id = @iId
print @sMsg
exec (@sMsg)
open Cur_Fields
Fetch Cur_Fields into @sField, @iType
print @sField
while @@Fetch_Status = 0
begin
if @bInclude = 1
begin
select @nInx = charindex(@sField,@sInclude)
if @nInx = 0
select @bIncluded = 0
else
select @bIncluded = 1
end
else
select @bIncluded = 1
if @bExclude = 1
begin
select @nInx = charindex(@sField,@sExclude)
if @nInx = 0
select @bExcluded = 0
else
select @bExcluded = 1
end
else
select @bExcluded = 0
if @bIncluded = 1 and @bExcluded = 0
begin
if @bCutoff = 1
begin
select @sSQL
= 'declare Cur_Cnt Cursor for '
+ 'select count(distinct ' + @sField + ') '
+ 'from ' + @sDBName + '.' + @sOwner + '.' + @sTableName
execute (@sSQL)
open Cur_Cnt
Fetch Cur_Cnt into @iCount
/* DEBUG:
* select @sMsg = 'Field ' + @sField + ' has a count of '
* + convert(varchar,@iCount)
*/ close Cur_Cnt
deallocate Cur_Cnt
end
else
select @iCount = 2000000
if @iCount <= @iCutoff
begin
if @iType = 50
/* Bit Field */
select @sSQL = 'select convert(tinyint,' + @sField + ') as ' + @sField
+ ', ''Count'' = count(*)'
+ ' from ' + @sDBName + '.' + @sOwner + '.' + @sTableName
+ ' group by convert(tinyint,' + @sField + ')'
+ ' order by convert(tinyint,' + @sField + ')'
else
select @sSQL = 'select ' + @sField + ', ''Count'' = count(*)'
+ ' from ' + @sDBName + '.' + @sOwner + '.' + @sTableName
+ ' group by ' + @sField
+ ' order by ' + @sField
execute (@sSql)
end
else
begin
select @sMsg = 'Field ' + @sField + ' has been excluded by count'
print @sMsg
end
end
else
begin
select @sMsg = 'Field ' + @sField + ' has been excluded'
print @sMsg
end
Fetch Cur_Fields into @sField, @iType
end
close Cur_Fields
deallocate Cur_Fields
end
end
set nocount off
return 0
GO 
