数据库校验和

介绍 

有时需要确定数据库是否已更改，如果是，哪些表已更改。通常可以使用日志分析来完成。但是，如果数据库大小不是太大，则使用此代码会更容易。

使用代码 

我编写了一个 Microsoft SQL Server 存储过程，用于计算当前数据库中所有表或仅某些表的校验和。该过程基于所有字段使用
物理顺序返回一个整数。

它使用 Microsoft SQL Server 的本机 Checksum 函数。用户可以选择指定一个文件模型。如果发现数据已更改，可以查看详细日志以获取更多详细信息。

语法

 CheckSumDB @Verbose [ @Exit @TabExit  @Model ]
           @Verbose : H  - 此帮助。
                              R - 显示最终校验和（默认）
                                  A - 显示所有表的校验和
                              N - 无输出
             @Exit    :  结果输出
             @TabExit :  输出表，包含表名和校验和字段
      @Model   :  表名应包含的子字符串。默认所有

示例

      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

源代码    

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
 

关注点   

这是一个使用 Sysobjects 的高级动态 SQL 编程的良好示例。