Click here to Skip to main content
12,996,367 members (71,482 online)
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 27 Nov 2012

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.


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.


 CheckSumDB @Verbose [ @Exit <code>@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


     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)=''
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'
   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'

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
   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

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


-- Scan tables from current database
   IF  @@FETCH_STATUS <> 0  Break    -- No more tables

                     -- File model OK?
   IF ( @Model='' OR  @Tabela  LIKE '%' + @Model + '%' ) and (@Tabela <> @TabExit)
   	Set @Sql= N'Select @Aux = Avg(Cast(CHECKSUM(*) As Float)) From dbo.' +
   	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
       	   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
   	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


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

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

Points of Interest   

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


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


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.

You may also be interested in...


Comments and Discussions

QuestionChecksum for a select statement or table Pin
Hennie113-Apr-14 21:47
memberHennie113-Apr-14 21:47 
AnswerRe: Checksum for a select statement or table Pin
Paulo Buchsbaum16-Jun-14 17:24
memberPaulo Buchsbaum16-Jun-14 17:24 
GeneralMy vote of 5 Pin
magicpapacy12-Dec-12 18:57
membermagicpapacy12-Dec-12 18:57 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170609.2 | Last Updated 27 Nov 2012
Article Copyright 2012 by Paulo Buchsbaum
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid