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

Rate

Share

Share

Rate