統計資料庫所有表的筆數與完整性校驗（CheckSum）

今天來分享幾個在 SQL Server 2008 裡，如何比對資料表內容的小技巧，讓你可以快速檢查資料是否有異動。以下會介紹三種方法，分別適用於不同的情境，親自實測過，效果滿不錯的喔！

---

方法 1：用 CHECKSUM_AGG 來計算表格變更

這個方法是利用 CHECKSUM_AGG(BINARY_CHECKSUM(*)) 來算出整個資料表的檢查碼。它的優點就是運算速度快，不會遇到像 XML 轉換那種限制，缺點則是有可能會發生碰撞（就是不同資料卻得到一樣的檢查碼），而且對於 BLOB 或浮點數欄位就不太適合。

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS TableChecksum
FROM sys.tables t
         INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'dbo'
GROUP BY s.name, t.name
ORDER BY s.name, t.name;

小提醒：

• BINARY_CHECKSUM(*) 會根據一筆資料的所有欄位來算出一個整數檢查碼。
• CHECKSUM_AGG 則是把每筆資料的檢查碼加總起來，得出整張表的綜合檢查碼。

---

方法 2：統計各資料表的筆數

如果你只是想知道每張資料表有多少筆資料，不用一個個 COUNT(*) 的話，可以利用系統檢視表 sys.dm_db_partition_stats，它可以很快地幫你統計 Heap 表和 Clustered Index 的資料列數。

SELECT s.name           AS SchemaName,
       t.name           AS TableName,
       SUM(p.row_count) AS [RowCount]
FROM sys.tables t
         INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
         INNER JOIN sys.dm_db_partition_stats p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1) -- 0：Heap 表，1：Clustered Index
GROUP BY s.name, t.name
ORDER BY s.name, t.name;

重點：

• 直接從系統檢視表取得筆數，比 COUNT(*) 效能更好！
• 記得用 index_id IN (0, 1) 過濾，只抓 Heap 表和 Clustered Index 的資料。

---

方法 3：利用 SHA-256 算雜湊值來驗證資料完整性

若你對資料的完整性要求非常高，想要用更強的雜湊演算法（比如 SHA-256），那你可以考慮這個方法。不過這個方法會比 CHECKSUM_AGG 慢一點，而且在處理大量資料時，轉換成 XML 的部分可能會有點吃力。

-- 若 TableCursor 已存在，先釋放
IF CURSOR_STATUS('local', 'TableCursor') >= -1
    BEGIN
        CLOSE TableCursor;
        DEALLOCATE TableCursor;
    END

-- 建立臨時表來存放雜湊結果
CREATE TABLE #HashResults
(
    TableName NVARCHAR(300),
    TableHash VARBINARY(32) -- SHA2_256 的雜湊值長度為 32 bytes
);

DECLARE @FullTableName NVARCHAR(300),
    @SQL NVARCHAR(MAX);

-- 取得 dbo schema 下所有資料表
DECLARE TableCursor CURSOR LOCAL FOR
    SELECT QUOTENAME(s.name) + '.' + QUOTENAME(t.name) AS FullTableName
    FROM sys.tables t
         INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name = 'dbo';

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @FullTableName;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = '
    SELECT ''' + @FullTableName + ''' AS TableName,
           HASHBYTES(''SHA2_256'', (SELECT * FROM ' + @FullTableName + ' FOR XML AUTO)) AS TableHash;';

        -- 執行動態 SQL 並將結果插入臨時表
        INSERT INTO #HashResults
            EXEC sp_executesql @SQL;

        FETCH NEXT FROM TableCursor INTO @FullTableName;
    END

CLOSE TableCursor;
DEALLOCATE TableCursor;

-- 輸出所有雜湊結果
SELECT *
FROM #HashResults;

說明一下：

• 這段程式碼利用動態 SQL 搭配 HASHBYTES('SHA2_256', ...) 來計算每張表的 SHA-256 雜湊值，並用 FOR XML AUTO 把表格內容轉換成 XML 字串。
• 為避免資源被 Cursor 長期佔用，程式碼開頭先檢查並釋放現有的 Cursor。
• 最後將所有結果匯總到臨時表，再一次性輸出。

---

總結一下三種方法

方法	適用情境	速度	準確性	可能遇到的問題
CHECKSUM_AGG(BINARY_CHECKSUM(*))	快速檢查資料變更	★★★★★	★★☆☆☆	可能會碰撞，不適用部分欄位類型
sys.dm_db_partition_stats	統計各表筆數	★★★★★	N/A	只能統計筆數，無法檢查內容變更
HASHBYTES(SHA2_256, FOR XML AUTO)	精確驗證資料完整性	★★☆☆☆	★★★★★	XML 轉換可能對大表有負擔

如果你只是需要快速確認資料是否有異動，使用 CHECKSUM_AGG 是最方便又快速的方法；如果對資料完整性要求較高，又不怕花點資源，那就可以考慮用 SHA-256 的方式。不管是哪種方法，依照你的需求做選擇，才能兼顧效率和準確性！

希望這篇文章對你有幫助，有任何問題或心得歡迎留言分享！Happy coding～～

---

後來有 DBA 提供的另外的寫法

DECLARE @SQL NVARCHAR(MAX);
DECLARE @CountSQL NVARCHAR(MAX);
DECLARE @TableName NVARCHAR(128);
DECLARE @SchemaName NVARCHAR(128);
DECLARE @RecordCount BIGINT;
DECLARE @HasPK BIT;
DECLARE @PKColumns NVARCHAR(MAX);
DECLARE @MaxRows INT = 1000; -- 設定最大資料列數
DECLARE @DataSize INT = 4000; -- 設定最大資料大小，避免超過8000位元組的限制

-- 建立暫存表來存儲結果
IF OBJECT_ID('tempdb..#TableHashes') IS NOT NULL
    DROP TABLE #TableHashes;

CREATE TABLE #TableHashes (
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    TableHash VARBINARY(20),
    RecordCount BIGINT,
    UsedPK BIT,
    ErrorMessage NVARCHAR(MAX) NULL
);

-- 取得所有使用者資料表
DECLARE TableCursor CURSOR FOR
SELECT s.name AS SchemaName, t.name AS TableName
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
--WHERE t.name = 'JMC_SMS'; -- 加入WHERE條件指定表名

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 計算資料筆數
    SET @CountSQL = N'SELECT @RecordCount = COUNT(*) FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
    EXEC sp_executesql @CountSQL, N'@RecordCount BIGINT OUTPUT', @RecordCount OUTPUT;
    
    -- 檢查表是否有主鍵
    SET @HasPK = 0;
    SET @PKColumns = NULL;
    
    SELECT @HasPK = 1,
           @PKColumns = STUFF((
               SELECT ', ' + QUOTENAME(c.name)
               FROM sys.indexes i
               INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
               INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
               WHERE i.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
               AND i.is_primary_key = 1
               ORDER BY ic.key_ordinal
               FOR XML PATH('')
           ), 1, 2, '')
    FROM sys.indexes i
    WHERE i.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
    AND i.is_primary_key = 1;
    
    -- 資料筆數為0的表視為雜湊計算成功
    IF @RecordCount = 0
    BEGIN
        INSERT INTO #TableHashes (SchemaName, TableName, TableHash, RecordCount, UsedPK, ErrorMessage)
        VALUES (@SchemaName, @TableName, HASHBYTES('SHA1', ''), 0, @HasPK, NULL);
    END
    ELSE
    BEGIN
        -- 如果有主鍵，使用主鍵欄位計算雜湊值
        IF @HasPK = 1
        BEGIN
            SET @SQL = N'
            INSERT INTO #TableHashes (SchemaName, TableName, TableHash, RecordCount, UsedPK, ErrorMessage)
            SELECT ''' + @SchemaName + ''', ''' + @TableName + ''',
                   HASHBYTES(''SHA1'', SUBSTRING((SELECT TOP ' + CAST(@MaxRows AS NVARCHAR(10)) + ' ' + @PKColumns + ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ORDER BY ' + @PKColumns + ' FOR XML RAW, BINARY BASE64), 1, ' + CAST(@DataSize AS NVARCHAR(10)) + ')),
                   ' + CAST(@RecordCount AS NVARCHAR(20)) + ',
                   1, NULL';
        END
        ELSE
        BEGIN
            -- 無主鍵，使用所有欄位計算雜湊值
            SET @SQL = N'
            INSERT INTO #TableHashes (SchemaName, TableName, TableHash, RecordCount, UsedPK, ErrorMessage)
            SELECT ''' + @SchemaName + ''', ''' + @TableName + ''',
                   HASHBYTES(''SHA1'', SUBSTRING((SELECT TOP ' + CAST(@MaxRows AS NVARCHAR(10)) + ' * FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' FOR XML RAW, BINARY BASE64), 1, ' + CAST(@DataSize AS NVARCHAR(10)) + ')),
                   ' + CAST(@RecordCount AS NVARCHAR(20)) + ',
                   0, NULL';
        END
        
        BEGIN TRY
            EXEC sp_executesql @SQL;
        END TRY
        BEGIN CATCH
            PRINT '錯誤處理表格: ' + @SchemaName + '.' + @TableName;
            
            -- 儲存錯誤資訊到表格
            INSERT INTO #TableHashes (SchemaName, TableName, TableHash, RecordCount, UsedPK, ErrorMessage)
            VALUES (@SchemaName, @TableName, NULL, @RecordCount, @HasPK, ERROR_MESSAGE());
        END CATCH
    END

    FETCH NEXT FROM TableCursor INTO @SchemaName, @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

-- 顯示結果
SELECT 
    SchemaName,
    TableName,
    CONVERT(VARCHAR(40), TableHash, 2) AS TableHashHex,
    RecordCount,
    CASE WHEN UsedPK = 1 THEN 'Yes' ELSE 'No' END AS UsedPrimaryKey,
    CASE 
        WHEN TableHash IS NULL THEN 'Failed to calculate hash'
        ELSE 'Success'
    END AS Status,
    ErrorMessage
FROM #TableHashes
ORDER BY SchemaName, TableName;

-- 清理暫存表
DROP TABLE #TableHashes;