EazyBI: Найди отличия в таблицах SQL

Понадобилось тут при переходе на новую систему сравнить данные справочников с архивной системой.

Для этих целей отлично пригодилась утилита Tablediff , главное требование чтобы таблицы имели одинаковую структуру и содержали первичный ключ либо столбец идентификатора identity, rowguid или уникальный ключ.
Плюс этой утилиты в том что можно легко сравнивать таблицы с разных серверов и конечно же есть возможность привести таблицы к единому виду добавив всего лишь один параметр -f и получить необходимый скрипт .

Если таблицы не совпадают по структуре проще написать свой запрос для сравнения используя связанные серверы и команды INTESECT и EXCEPT или создать представления и сравнить их.

Утилита устанавливается вместе с опцией Репликация на SQL сервер, так что можно скопировать к себе с любого доступного сервера

Обычно утилита располагается по адресу C:\Program Files\Microsoft SQL Server\110\COM

В самом примитивном виде команда выглядит так

tablediff.exe" -sourceserver MyServer1 -sourcedatabase MyDatabase1 -sourceschema SourceSchema -sourcetable MyTable1 -destinationserver MyServer1 -destinationdatabase MyDatabase1 -destinationschema DestSchema -destinationtable MyTable2 -dt -et TableDiff -o C:\Shared\Cmp_tables_sql\TableDiff.txt -f C:\Shared\Cmp_tables_sql\Script\Tablediff.sql

результат пишется в файл TableDiff.txt и таблицу TableDiff

для сравнения результатов генерируем скрипты на основе таблицы результата, экспортировать результаты будем утилитой командной строки sqlcmd

SELECT *,

'start /wait sqlcmd -S testserver -d Finance -E -Q "'+match+'" -o "C:\Shared\Cmp_tables_sql\Export\'+t.t_name+'_match.csv" -W -w 1024 -s";"' export_match,

(CASE WHEN Diff_completed = 1 THEN 'start /wait sqlcmd -S testserver -d Finance -E -Q "'+mismatch+'" -o "C:\Shared\Cmp_tables_sql\Export\'+t.t_name+'_mismatch.csv" -W -w 1024 -s";"' ELSE '' END) export_mismatch,

(CASE WHEN Diff_completed = 1 THEN 'start /wait sqlcmd -S testserver -d Finance -E -Q "'+src_only+'" -o "C:\Shared\Cmp_tables_sql\Export\'+t.t_name+'_SourceOnly.csv" -W -w 1024 -s";"' ELSE '' END) export_src_only,

(CASE WHEN Diff_completed = 1 THEN 'start /wait sqlcmd -S testserver -d Finance -E -Q "'+dest_only+'" -o "C:\Shared\Cmp_tables_sql\Export\'+t.t_name+'_DestOnly.csv" -W -w 1024 -s";"' ELSE '' END) export_dest_only

FROM

(

SELECT s.name s_name, o.name t_name , o.[type] t_type, (CASE WHEN OBJECT_ID('dbo.'+o.name+'_diff', 'U') IS NOT NULL THEN 1 ELSE 0 END) Diff_completed ,

'start /wait tablediff.exe -sourceserver testserver -sourcedatabase Finance -sourceschema Source -sourcetable '+o.name+' -destinationserver testserver -destinationdatabase Finance -destinationschema Dest -destinationtable '+o.name+' -dt -et '+o.name+'_diff -o C:\Shared\Cmp_tables_sql\'+o.name+'_diff.txt' TABLEDIFF_CMD,

'--'+o.name + CHAR(13) +CHAR(10) as t_name2,

'SELECT [MSdifftool_ErrorDescription] , COUNT(*) AS CNT , (SELECT COUNT(*) FROM Dest.'+o.name+') Dest_CNT, (SELECT COUNT(*) FROM Source.'+o.name+') Source_CNT FROM [FINANCE].[dbo].['+o.name+'_diff] GROUP BY [MSdifftool_ErrorDescription]' AS "Количество",

'SELECT * FROM [Source].'+o.name+' WHERE CODE IN (SELECT D.CODE FROM [FINANCE].[dbo].['+o.name+'_diff] D WHERE D.[MSdifftool_ErrorDescription] = N''Src. Only'')' src_only,

'SELECT * FROM [Dest].'+o.name+' WHERE CODE IN (SELECT D.CODE FROM [FINANCE].[dbo].['+o.name+'_diff] D WHERE D.[MSdifftool_ErrorDescription] = N''Dest. Only'')' dest_only,

'SELECT ''Source'' AS syst , * FROM [Source].'+o.name+' WHERE CODE IN (SELECT D.CODE FROM [FINANCE].[dbo].['+o.name+'_diff] D WHERE D.[MSdifftool_ErrorDescription] = N''Mismatch'') UNION ALL SELECT ''Dest'' AS syst , * FROM [Dest].'+o.name+' WHERE CODE IN (SELECT D.CODE FROM [FINANCE].[dbo].['+o.name+'_diff] D WHERE D.[MSdifftool_ErrorDescription] = N''Mismatch'') ORDER BY [Code]' mismatch,

'SELECT * FROM [Source].'+o.name+' INTERSECT SELECT * FROM [Dest].'+o.name AS match,

'DROP TABLE ' + '[Dest].'+o.name AS "Удалить таблицу",

'DROP TABLE ' + '[Source].'+o.name AS "Удалить таблицу 2"

from sys.objects o

inner join sys.schemas s on s.schema_id = o.schema_id

WHERE s.name = 'Source' AND TYPE = N'U'

) T

ORDER BY s_name , t_name

Берем созданные команды выше для tablediff.exe и делаем из них bat файл

Запускаем наш файл и идем пить кофе

Результаты можно будет найти в указанной папке, для кого-то это достаточно,
но иногда важно видеть полные отличия.

Для этого копируем скрипты для сравнения сгенерированные выше, при необходимости правим и запускаем , после уже смотрим результаты, так уже более нагляднее.