sp_gen_checksum_selects

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