五餅二魚工作室

這幾天遇到一個朋友求助，他表示他有個資料庫，某一天忽然因為程式異常，造成某些資料表的資料被竄改了，他想利用舊的資料庫備份，來做資料表的比較，看看實際到底被改了哪些資料，以利後續的補救措施。

表面上看起來這是一個簡單的問題，只要把目前的資料庫內的資料表，和舊的資料庫內的資料表，利用 INNER JOIN 去關聯 Primary Key 的欄位，然後再去比對其他欄位是否有不同，應該就可以結束了。但因為資料庫內的資料表非常多，且很多資料表都有 100 個以上的欄位，那個 SQL 雖然可以寫得出來，但實在也是非常的累人，因此朋友詢問是否有好的方法，而不用那麼苦命的一個欄位一個欄位的去寫 SQL。

方法是有的，這裡我拿一個 DB1 的資料庫來做展示，從下圖中可以看出，在這個資料表內有一個 customers 的資料表，他的 PK 是 id 這個欄位，在這個資料表內有不少的資料。

而另外也有一個資料庫 DB2，裡面也有一個 customers 的資料表，那我們該如何找出這兩個資料表中有差異的資料呢 ?

這裡我們主要是搭配 checksum 這個函數，他是從 SQL Server 2008 就開始有提供的一個函數，因此我們可以很容易地利用這個函數，算出每一筆資料的 Checksum 的值

因此就可以用這樣的方式來做個延伸，搭配 CTE 的語法來做使用，這樣就可以很容易找到差異的資料了

with new( id,[checksum]) as
(
	select id,CHECKSUM(*) from [DB1].[dbo].[customers]
), old( id,[checksum]) as
(
	select id,CHECKSUM(*) from [DB2].[dbo].[customers]
), diff( id ) as
(
select new.id  
from new 
join old ON new.id = old.id and new.[checksum] <> old.[checksum]
)
select 'new' as dbname, a.* from [DB1].[dbo].[customers] a join diff ON a.id = diff.id
union
select 'old' as dbname, b.* from [DB2].[dbo].[customers] b join diff ON b.id = diff.id
order by id , dbname

---

上述方式雖然可以針對單一的資料表來做比對，但兩個資料庫內要怎麼知道那些資料表有差異呢 ? 在這裡可以另外搭配 CHECKSUM_AGG 的函數，這個跟 CHECKSUM 最大的差異在於 CHECKSUM 函數主要是針對 ROW 去計算，但 CHECKSUM_AGG 函數主要是針對 COLUMN 來計算，因此這裡我們可以先用類似以下的語法來做處理

with new( tablename, checksum )  as
(
select 'customers' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB1].[dbo].[customers]
union 
select 'employees' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB1].[dbo].[employees]
union 
select 'orders' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB1].[dbo].[orders]
union 
select 'order_details' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB1].[dbo].[order_details]
union 
select 'products ' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB1].[dbo].[products]
), old( tablename, checksum )  as
(
select 'customers' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB2].[dbo].[customers]
union 
select 'employees' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB2].[dbo].[employees]
union 
select 'orders' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB2].[dbo].[orders]
union 
select 'order_details' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB2].[dbo].[order_details]
union 
select 'products ' as tablename,CHECKSUM_AGG( CHECKSUM(*)) as checksum from [DB2].[dbo].[products]
)
select new.* from new join old on new.tablename=old.tablename and new.checksum<>old.checksum

 

在上述的範例中，我們先用 CHECKSUM 這個函數，針對每個 ROW 去計算出一個檢查值，然後再用 CHECKSUM_AGG 的函數，針對所有的資料的檢查碼，再去算一個新的檢查碼，因此兩個資料表要是這個值不相同，那就應該是兩個資料庫內有資料表內的資料不一致的情況發生，那麼在配合一開始的處理，就可以找到真正有差異的資料。