计算SQL Server中表的哈希或校验和 [英] Calculate Hash or Checksum for a table in SQL Server

问题描述

我正在尝试为SQL Server 2008中的整个表计算校验和或哈希.我遇到的问题是该表包含XML列数据类型，校验和不能使用它，而必须首先转换为nvarchar.因此，我需要将其分解为两个问题:

I'm trying to compute a checksum or a hash for an entire table in SQL Server 2008. The problem I'm running into is that the table contains an XML column datatype, which cannot be used by checksum and has to be converted to nvarchar first. So I need to break it down into two problems:

1. 计算一行的校验和，架构在运行时之前是未知的.
2. 计算所有行的校验和以获得完整的表校验和.

推荐答案

您可以使用

You can use CHECKSUM_AGG. It only takes a single argument, so you could do CHECKSUM_AGG(CHECKSUM(*)) - but this doesn't work for your XML datatype, so you'll have to resort to dynamic SQL.

您可以从INFORMATION_SCHEMA.COLUMNS动态生成列列表，然后将int插入模板:

You could generate dynamically the column list from INFORMATION_SCHEMA.COLUMNS and then insert int into a template:

SELECT @column_list = COALESCE(@column_list + ', ', '')
        + /* Put your casting here from XML, text, etc columns */ QUOTENAME(COLUMN_NAME)
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = @table_name
    AND TABLE_SCHEMA = @schema_name

DECLARE @template AS varchar(MAX)
SET @template = 'SELECT CHECKSUM_AGG(CHECKSUM({@column_list})) FROM {@schema_name}.{@table_name}'

DECLARE @sql AS varchar(MAX)
SET @sql = REPLACE(REPLACE(REPLACE(@template
    '{@column_list}', @column_list),
    '{@schema_name}', @schema_name),
    '{@table_name}', @table_name)

EXEC ( @sql )

这篇关于计算SQL Server中表的哈希或校验和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！