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

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

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

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

Утилита устанавливается вместе с опцией Репликация на SQL сервер, так что можно скопировать к себе с любого доступного сервера
Обычно утилита располагается по адресу C:Program FilesMicrosoft SQL Server110COM
В самом примитивном виде команда выглядит так
tablediff.exe» -sourceserver MyServer1 -sourcedatabase MyDatabase1  -sourceschema SourceSchema -sourcetable MyTable1 -destinationserver MyServer1 -destinationdatabase MyDatabase1 -destinationschema DestSchema -destinationtable MyTable2 -dt -et TableDiff -o C:SharedCmp_tables_sqlTableDiff.txt -f C:SharedCmp_tables_sqlScriptTablediff.sql
результат пишется в файл TableDiff.txt  и таблицу TableDiff
для сравнения результатов генерируем скрипты на основе таблицы результата, экспортировать результаты будем утилитой командной строки sqlcmd
SELECT *,
‘start /wait  sqlcmd -S testserver -d Finance -E -Q «‘+match+‘» -o «C:SharedCmp_tables_sqlExport’+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:SharedCmp_tables_sqlExport’+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:SharedCmp_tables_sqlExport’+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:SharedCmp_tables_sqlExport’+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:SharedCmp_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’&
#39;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 файл
Запускаем наш файл  и идем пить кофе

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

 

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

Если же необходимо сделать таблицы идентичными добавляем параметр

-f C:SharedCmp_tables_sqlScriptsSegmentAcceptors_conv.sql

и выполняем результирующий скрипт

 

Автор: Roman D