Click here to Skip to main content
Click here to Skip to main content
Go to top

Checksum for Database

, 27 Nov 2012
Rate this:
Please Sign up or sign in to vote.
A simple way to generate checksum for tables and databases.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Paulo Buchsbaum
President Great Solutions
Brazil Brazil
Paulo Buchsbaum is from Rio de Janeiro, Brazil with Information Technology Master at PUC-RJ. He has been a business advisor and instructor for over 20 years (Vale, Brahma, Cosigua, Golden Cross, Casa & Video, etc.) and was a former UFF and PUC-RJ teacher.
 
He have written 3 books, 2 of them in co-autorship: "Frases Geniais - que você gostaria de ter dito" (2004), "Do Bestial do Genial - Frases da Política" (2006) e "Negócios S/A - Administração na Prática" (2011).
 
His main focus includes Qant Methods, Management, Finance, Valuation, Retail and Informatics. Also works in Marketing and Logistics, with a analytic view. Besides he've depeened in Decision Theory, Critical Thinking, Evolutionary Psychology and Behavioral Economics.

Comments and Discussions

 
QuestionChecksum for a select statement or table PinmemberHennie113-Apr-14 21:47 
AnswerRe: Checksum for a select statement or table PinmemberPaulo Buchsbaum16-Jun-14 17:24 
GeneralMy vote of 5 Pinmembermagicpapacy12-Dec-12 18:57 
Good Article

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 27 Nov 2012
Article Copyright 2012 by Paulo Buchsbaum
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid