Calculez le hachage ou la somme de contrôle pour une table dans SQL Server
J'essaie de calculer une somme de contrôle ou un hachage pour une table entière dans SQL Server 2008. Le problème que je rencontre est que la table contient un type de données de colonne XML, qui ne peut pas être utilisé par la somme de contrôle et doit d'abord être converti en nvarchar. J'ai donc besoin de le décomposer en deux problèmes:
- calculez une somme de contrôle pour une ligne, le schéma est inconnu avant l'exécution.
- calculer la somme de contrôle pour toutes les lignes pour obtenir le tableau complet de la somme de contrôle.
3 réponses
Vous pouvez utiliser CHECKSUM_AGG. Cela ne prend qu'un seul argument, donc vous pouvez faire CHECKSUM_AGG(CHECKSUM(*)) - mais cela ne fonctionne pas pour votre type de données XML, vous devrez donc recourir à SQL dynamique.
Vous pouvez générer dynamiquement la liste de colonnes à partir de INFORMATION_SCHEMA.COLUMNS, puis insérer int dans un modèle:
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(
'{@column_list}', @column_list),
'{@schema_name}', @schema_name),
'{@table_name}', @table_name)
EXEC ( @sql )
J'ai modifié le script pour générer une requête pour toutes les tables dans une base de données.
USE myDatabase
GO
DECLARE @table_name sysname
DECLARE @schema_name sysname
SET @schema_name = 'dbo'
DECLARE myCursor cursor
FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_SCHEMA = @schema_name
AND T.TABLE_TYPE = 'BASE TABLE'
AND T.TABLE_NAME NOT LIKE 'MSmerge%'
AND T.TABLE_NAME NOT LIKE 'sysmerge%'
AND T.TABLE_NAME NOT LIKE 'tmp%'
ORDER BY T.TABLE_NAME
OPEN myCursor
FETCH NEXT
FROM myCursor
INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @column_list nvarchar(MAX)
SET @column_list=''
SELECT @column_list = @column_list + CASE WHEN DATA_TYPE IN ('xml','text','ntext','image sql_variant') THEN 'CONVERT(nvarchar(MAX),'
ELSE ''
END
+ QUOTENAME(COLUMN_NAME)
+ CASE WHEN DATA_TYPE IN ('xml','text','ntext','image sql_variant') THEN ' /* ' + DATA_TYPE + ' */)'
ELSE ''
END + ', '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Table_name
ORDER BY ORDINAL_POSITION
SET @column_list = LEFT(@column_list, LEN(@column_list)-1) -- remove trailing comma
DECLARE @sql AS nvarchar(MAX)
SET @sql = 'SELECT ''' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ''' table_name,
CHECKSUM_AGG(CHECKSUM(' + @column_list + ')) CHECKSUM
FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@Table_name) + ' WITH (NOLOCK)'
PRINT @sql
FETCH NEXT
FROM myCursor
INTO @table_name
IF @@FETCH_STATUS = 0
PRINT 'UNION ALL'
END
CLOSE myCursor
DEALLOCATE myCursor
GO
/ / somme de hachage rapide de SQL et c # mirror Ukraine //HASH_ZKCRC64 ///-------------------------------------------------------------------------------------------------------------- privé Int64 HASH_ZKCRC64 (byte[] données) { Int64 résultat = 0x555555555555555555; si (Data = = null / / Data.Longueur
for (int i = 0; i < LeftLimit; i += 64)
{
Result = Result
^ BitConverter.ToInt64(Data, i)
^ BitConverter.ToInt64(Data, i + 8)
^ BitConverter.ToInt64(Data, i + 16)
^ BitConverter.ToInt64(Data, i + 24)
^ BitConverter.ToInt64(Data, i + 32)
^ BitConverter.ToInt64(Data, i + 40)
^ BitConverter.ToInt64(Data, i + 48)
^ BitConverter.ToInt64(Data, i + 56);
if ((Result & 0x0000000000000080) != 0)
Result = Result ^ BitConverter.ToInt64(Data, i + 28);
}
if (Ost > 0)
{
byte[] Bufer = new byte[SizeGlobalBufer];
Array.Copy(Data, LeftLimit, Bufer, 0, Ost);
for (int i = 0; i < SizeGlobalBufer; i += 64)
{
Result = Result
^ BitConverter.ToInt64(Bufer, i)
^ BitConverter.ToInt64(Bufer, i + 8)
^ BitConverter.ToInt64(Bufer, i + 16)
^ BitConverter.ToInt64(Bufer, i + 24)
^ BitConverter.ToInt64(Bufer, i + 32)
^ BitConverter.ToInt64(Bufer, i + 40)
^ BitConverter.ToInt64(Bufer, i + 48)
^ BitConverter.ToInt64(Bufer, i + 56);
if ((Result & 0x0000000000000080)!=0)
Result = Result ^ BitConverter.ToInt64(Bufer, i + 28);
}
}
byte[] MiniBufer = BitConverter.GetBytes(Result);
Array.Reverse(MiniBufer);
return BitConverter.ToInt64(MiniBufer, 0);
#region SQL_FUNCTION
/* CREATE FUNCTION [dbo].[HASH_ZKCRC64] (@data as varbinary(MAX)) Returns bigint
AS
BEGIN
Declare @I64 as bigint Set @I64=0x5555555555555555
Declare @Bufer as binary(8000)
Declare @i as int Set @i=1
Declare @j as int
Declare @Len as int Set @Len=Len(@data)
if ((@data is null) Or (@Len<=0)) Return 0
While @i<=@Len
Begin
Set @Bufer=Substring(@data,@i,8000)
Set @j=1
While @j<=8000
Begin
Set @I64=@I64
^ CAST(Substring(@Bufer,@j, 8) as bigint)
^ CAST(Substring(@Bufer,@j+8, 8) as bigint)
^ CAST(Substring(@Bufer,@j+16,8) as bigint)
^ CAST(Substring(@Bufer,@j+24,8) as bigint)
^ CAST(Substring(@Bufer,@j+32,8) as bigint)
^ CAST(Substring(@Bufer,@j+40,8) as bigint)
^ CAST(Substring(@Bufer,@j+48,8) as bigint)
^ CAST(Substring(@Bufer,@j+56,8) as bigint)
if @I64<0 Set @I64=@I64 ^ CAST(Substring(@Bufer,@j+28,8) as bigint)
Set @j=@j+64
End;
Set @i=@i+8000
End
Return @I64
END
*/
#endregion
}