Checksum for Database

Introduction 

Sometimes one need to discover if a database has changed and,, if so, what tables has changed. Normally  it can be done  with log analysis. However if database size is not too big, it is easier to use this code.

Using the code 

I've written a Microsoft SQL Server Stored Procedure for calculating checksum for all tables in the current database or just for some tables.  The procedure returns a integer number based on all fields using the
physical order. 

It uses the native Checksum function from Microsoft SQL Server. The user can optionally specify a file model.  If one notice that data has changed,, one can view the detailed logs for more details.

Syntax

 CheckSumDB @Verbose [ @Exit @TabExit  @Model ]
           @Verbose : H  - This help.
                              R - It shows final checksum (default)
                              A - It shows checksum for all tables
                              N - No output
             @Exit    :  Result output
             @TabExit :  Output Table with Table name and Checksum fields
      @Model   :  Substring that Table name should contain. Default all

Example

      Exec CheckSumDB N, @Check Output, 'Tb_Checksum' 
 --   Calculates checksum for database and print its Checksum
 --   Returning checksum in the @Check variable, with no messages
 --   Return checksum for all tables inside table TB_Checksum

Source Code     

Create  PROCEDURE CheckSumDB
 @Verbose Char(1)='R',
 @Exit Int=0 OutPut, 
 @TabExit varchar(20)='',
 @Model varchar(20)=''
AS  
Begin
Declare @Tot Bigint           --  Final Checksum
Declare @Conta Int            --  File Count
Declare @CS Int               --  File Checksum 
Declare @Aux Int              --  Brute File Checksum
Declare @Tabela Varchar(30)   -- Table Name
Declare @SQL NVarChar(1000)   -- SQL Instruction
Declare @Params NVarchar(30)  -- Params from SQL Instruction
Declare @HasFile Bit          -- 1 if there is a exit file
Declare @HasModel Bit         -- 1 if there is any model for table names
Declare @File NVarchar(30)     -- Exit file
Declare @Mess Varchar(25)      -- Messagem for print

Set NoCount On
Set @Verbose = Upper(@Verbose)
if @Verbose='?' or @Verbose='H'
begin
   Print 'CheckSumDB - Return checksum as a integer, in order to check'
   Print '             if there was any changes between two distinct moments.' 
   Print ''
   Print 'SYNTAX:  CheckSumDB @Verbose [ @Exit @TabExit @Model ]'
   Print '   @Verbose :  H - This help'
   Print '               R - It shows final checksum (default)'
   Print '               A - It shows checksum for all tables' 
   Print '               N - No output'
   Print '   @Exit    :  Result output'
   Print '   @TabExit :  Output Table with Table name and Checksum fields'
   Print '   @Model   :  Substring that Table name should contain. Default all'
   Print ''
   Print 'Example:'
   Print '    Exec CheckSumDB N @Check Output - Calculates checksum for database'
   Print '         returning in the @Check variable, with no messages'
   Return        
end

Set @HasFile = Case When @TabExit='' Then 0 Else 1 End -- Is there a Exit File?
Set @HasModel = Case When @Model='' Then 0 Else 1 End -- Is there a Model?
Set @Tot=0   -- Total Checksum
Set @Conta=0  -- File count

IF @HasFile=1 -- Create exit table
begin
   Set @File = quotename(@TabExit)
   Set @Sql = N'if object_id(''dbo.' + @File +
              N''') is not null Drop Table ' + @File +
	      N' Create Table ' + @File +
	      N' (Tab varchar(30), CheckS Int)'
   Exec sp_executesql @Sql
end   

Declare C Cursor Fast_Forward  -- Fast read-only cursor
For
	SELECT  O.Name  As Tabela
	From sysobjects As O
	INNER JOIN sys.partitions As p
	On O.id=P.object_id
	WHERE O.XType='U' and p.index_id IN (0, 1) and P.rows>0

OPEN C

-- Scan tables from current database
WHILE 1=1
begin
   FETCH NEXT FROM C INTO @Tabela
   IF  @@FETCH_STATUS <> 0  Break    -- No more tables

                     -- File model OK?
   IF ( @Model='' OR  @Tabela  LIKE '%' + @Model + '%' ) and (@Tabela <> @TabExit)
                    
   begin
   	Set @Sql= N'Select @Aux = Avg(Cast(CHECKSUM(*) As Float)) From dbo.' +
                 	QuoteName(@Tabela)
   	Set @Params = N'@Aux Int OUTPUT'
   	Exec sp_executesql @Sql, @Params, @Aux = @Aux Output
   	Set @CS = Abs(Floor(@Aux))
	
   	IF @HasFile=1  -- Insert checkum table in the exit table
   	begin
	
       	   Set @Sql = N'Insert Into ' + @File + N' Select @Tabela , @CS'
 	   Set @Params  = N'@Tabela Varchar(30), ' +  N'@CS Int'
     	   Exec sp_executesql @Sql, @Params, @Tabela, @CS
   	end   
	
   	if @verbose = 'A' 
       	  Print 'Checksum for table ' + @Tabela + ': ' +
		    Replace(Convert(Varchar,CONVERT(Money,@CS),1), '.00','')

   	Set @Conta = @Conta + 1   -- Count Tables
   	Set @Tot = @Tot + @CS    -- Accumulate table checksum  
  end -- Model
end -- Scanning Files

CLOSE C
DEALLOCATE C

if @Conta=0
  Set @Exit=0
else
  Set @Exit =Floor( @Tot / @Conta )

if @verbose = 'A' or @verbose='R'
   if @Conta=0 
      Print 'No files found'
   else
   begin 
     Set @Mess = Replace(Convert(Varchar,CONVERT(Money,@Exit),1), '.00','')
     if @Model=''
        Print 'Database Checksum: ' + @Mess
     else
        Print 'Checksum for model ' + @Model + ': ' + @Mess
   end
end
 

Points of Interest   

It's a nice example of advanced dynamic SQL programming with Sysobjects use.