sp_gen_checksum_selects

,

Compile in master. Set your QA output to text. When run from the db of interest it generates checksum select statements for each user table in the db composed of the PK columns and a checksum for all columns excluding text, ntext, image and sql-variant. There are no parameters. Modify for your own use. Save your output to a sql file. If any tables exist in the db that do not have a PK then those are listed at the end commented out.

Checksum can use (*) unless text, ntext, image and sql-variant datatypes exist otherwise it must use a specified list excluding columns having these datatypes.

The order is by table id not table name.

if exists (select * 
             from dbo.sysobjects 
            where id = object_id(N'[dbo].[sp_gen_checksum_selects]') 
              and OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure [dbo].[sp_gen_checksum_selects]
GO


Create procedure dbo.sp_gen_checksum_selects 
As

/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/
--Name        : sp_gen_checksum_selects        
--
--Description : Generates checksum select statments for each user table in a   
--              db composed of the PK columns and a checksum for all columns 
--              excluding text, ntext, image and sql-variant.             
--
--Parameters  : None
--
--Comments    : The order is by table id not name. If any tables exist in
--              the db that do not have a PK then those are listed at the
--              end commented out. 
--
--Notes       : Checksum can use (*) unless text, ntext, image and sql-variant
--              datatypes exist otherwise it must use a specified list 
--              excluding columns having these datatypes. 
--
--Date        : 04/14/2005
--Author      : Clinton Herring
--
--History     : Date Initials First change, etc.
--              
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/
       
-- set nocount on
Set nocount on

-- If temp table exists drop them before attempting to create them. 
If (Select object_id('tempdb.dbo.#usertbls')) > 0
   Exec ('Drop table #usertbls')

-- Create a usertbl list holding table
Create table #usertbls(
       id int null,
       name sysname null,
       uid int null,
       owner sysname null)

-- Declare variables
Declare @tblid int
Declare @tblname sysname
Declare @tbluid int
Declare @tblowner sysname
Declare @pkcols varchar(4000)
Declare @cols varchar(4000)

Set @pkcols = ''
Set @cols = ''

-- Load user tble list
Insert into #usertbls
Select a.id, a.name, a.uid, b.name
  from sysobjects a
  join sysusers b
    on a.uid = b.uid
   and a.type = 'U' 
   and a.status >= 0
  left outer join sysobjects c
    on a.id = c.parent_obj
   and c.type = 'K'
 where c.parent_obj is not null

-- Select the first table to process
Select @tblid = min(id) from #usertbls
While @tblid is not null
   Begin
      -- Get additional info.
      Select @tblname = name, @tblowner = owner, @tbluid = uid
        from #usertbls where id = @tblid 

      -- Get the pk column list.
      Select @pkcols = @pkcols + a.name + ', '
        from syscolumns a
        join sysobjects b 
          on a.id = b.id
         and b.type = 'U'
         and b.status >= 0
         and b.name = @tblname
         and b.uid = @tbluid
        join sysindexes c
          on b.id = c.id
      	and (c.status & 0x800) = 0x800 --PK index
        join syscolumns d --needed when PK is not 1st column
          on a.id = d.id
         and d.colid <= c.keycnt
      	and a.name = index_col (@tblowner + '.' + b.name, c.indid, d.colid)

       -- Get list from checksum function.
       If not exists(Select a.name 
                       from syscolumns a
                       join sysobjects b 
                         on a.id = b.id
                        and b.type = 'U'
                        and b.status >= 0
                        and b.name = @tblname
                        and b.uid = @tbluid
                        and a.xtype in (34,35,98,99))
          Begin
             Set @cols = '*'
          End
        Else
          Begin
             Select @cols = @cols + a.name + ','
                       from syscolumns a
                       join sysobjects b 
                         on a.id = b.id
                        and b.type = 'U'
                        and b.status >= 0
                        and b.name = @tblname
                        and b.uid = @tbluid
                        and a.xtype not in (34,35,98,99)
             Select @cols = substring(@cols,1,datalength(@cols)-1)
          End
      
      -- Display results for each table.
      Print '-- For table ' + @tblowner + '.' + @tblname
      Print 'Select ' + @pkcols + 'checksum(' + @cols + ') ' +
            'from ' + @tblowner + '.' + @tblname
      Print 'go' 

      -- Reset variables
      Set @pkcols = ''
      Set @cols = ''

      -- Get next table id.
      Select @tblid = min(id) from #usertbls where id > @tblid
   End

-- Check for table with no PK; if any print list.
If exists (Select a.name 
             from sysobjects a
             join sysusers b
               on a.uid = b.uid
              and a.type = 'U' 
              and a.status >= 0
             left outer join sysobjects c
               on a.id = c.parent_obj
              and c.type = 'K'
            where c.parent_obj is null)
   Begin
      -- List tables not having a PK and then remove them from the temp tbl.
      Print ''
      Print '/*'
      Print 'The following tables do not have a primary key and will no be included.'
      Select b.name + '.' + a.name 
        from sysobjects a
        join sysusers b
          on a.uid = b.uid
         and a.type = 'U' 
         and a.status >= 0
        left outer join sysobjects c
          on a.id = c.parent_obj
         and c.type = 'K'
       where c.parent_obj is null
       order by a.name
      Print '*/'
   End

GO

Rate

Share

Share

Rate