SqlServer迁移基础 --生成所迁移数据库所有表的tablediff脚本

https://docs.microsoft.com/zh-cn/sql/tools/tablediff-utility
https://docs.microsoft.com/zh-cn/sql/relational-databases/replication/administration/compare-replicated-tables-for-differences-replication-programming
tablediff 实用工具用于比较两个非收敛表中的数据，它对于排除复制拓扑中的非收敛故障非常有用。
借助SQLSERVER自带的tablediff工具，当初微软制作这个工具的目的就是用于比较复制中发布表和订阅表的数据一致

tablediff工具所在目录
C:Program FilesMicrosoft SQL Server100COMtablediff.exe

C:Program FilesMicrosoft SQL Server110COMtablediff.exe

C:Program FilesMicrosoft SQL Server120COMtablediff.exe

USE AdventureWorks2012
GO

-- declare public variables, need to init by user
DECLARE @source_Instance sysname,
        @source_Database sysname,
        @source_User sysname,
        @source_Passwd sysname,
        @destination_Instance sysname,
        @destination_Database sysname,
        @destination_User sysname,
        @destination_Passwd sysname,
        @diff_table_list NVARCHAR(MAX);

-- Public variables init.
SELECT @source_Instance = 'localhost',   -- Source Instance Name
       @source_Database = 'AdventureWorks2012',       -- Source Database is current database.
       @source_User = 'sa',                             -- Source Instance Connect User Name
       @source_Passwd = N'123456',                    -- Source Instance User Password
       @destination_Instance = N'127.0.0.1,2433',       -- Destination Instance Name
       @destination_Database = N'AdventureWorks2012', -- Destination Database name: NULL/empty: Keep the same as source db
       @destination_User = 'sa',                        -- Destination Instance User Name
       @destination_Passwd = N'123456',                -- Destination Instance User Password
       @diff_table_list = N''                     --NULL/empty: ALL Tables are needed to be diff.
;


-- Private variables, there is no need to init.
DECLARE @diff_table_list_xml XML,
        @timestamp CHAR(14);

-- correct the variables init by user.
SELECT @source_Instance = RTRIM(LTRIM(@source_Instance)),
       @source_Database = RTRIM(LTRIM(@source_Database)),
       @source_User = RTRIM(LTRIM(@source_User)),
       @source_Passwd = RTRIM(LTRIM(@source_Passwd)),
       @destination_Instance = RTRIM(LTRIM(@destination_Instance)),
       @destination_Database = CASE
                                   WHEN ISNULL(@destination_Database, N'') = N'' THEN
                                       @source_Database
                                   ELSE
                                       @destination_Database
                               END,
       @destination_User = RTRIM(LTRIM(@destination_User)),
       @destination_Passwd = RTRIM(LTRIM(@destination_Passwd)),
       @diff_table_list_xml
           = '<V><![CDATA['
             + REPLACE(
                          REPLACE(
                                     REPLACE(@diff_table_list, CHAR(10), ']]></V><V><![CDATA['),
                                     ',',
                                     ']]></V><V><![CDATA['
                                 ),
                          CHAR(13),
                          ']]></V><V><![CDATA['
                      ) + ']]></V>',
       @timestamp = REPLACE(REPLACE(REPLACE(CONVERT(CHAR(19), GETDATE(), 120), N'-', ''), N':', N''), CHAR(32), N'');

IF OBJECT_ID('tempdb..#tb_list', 'U') IS NOT NULL
DROP TABLE #tb_list;
CREATE TABLE #tb_list
(
    RowId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    TableName sysname NOT NULL
);

IF ISNULL(@diff_table_list, '') = ''
BEGIN
    INSERT INTO #tb_list
    SELECT name
    FROM sys.tables AS tb
    WHERE tb.is_ms_shipped = 0;
END;
ELSE
BEGIN
    INSERT INTO #tb_list
    SELECT table_name = T.C.value('(./text())[1]', 'sysname')
    FROM @diff_table_list_xml.nodes('./V') AS T(C)
    WHERE T.C.value('(./text())[1]', 'sysname') IS NOT NULL;
END;


SELECT 'tablediff.exe -sourceserver '+ @source_Instance+' -sourceuser '+@source_User+'  -sourcepassword '+ @source_Passwd+' -sourcedatabase ' +@source_Database+' -sourceschema '+sch.name+' -sourcetable '+ tb.name+' -destinationserver '+ @destination_Instance+' -destinationuser '+@destination_User+' -destinationpassword '+@destination_Passwd+' -destinationdatabase '+@destination_Database+' -destinationschema '+sch.name+' -destinationtable '+ tb.name+' -c -o '+@source_Database+'_TableDiff_'+ @timestamp + N'.txt' 
AS table_diff
FROM sys.tables AS tb
    LEFT JOIN sys.schemas AS sch
        ON tb.schema_id = sch.schema_id
WHERE tb.is_ms_shipped = 0
      AND tb.name IN (
                         SELECT TableName COLLATE Chinese_PRC_CI_AS FROM #tb_list
                     );

DROP TABLE #tb_list;

保存执行结果中table_diff列所有内容到文件table_diff.bat
执行table_diff.bat文件
检查table_diff.bat执行的日志文件

table_diff.bat批处理文件执行后会生成一个日志文件，日志文件的命名格式是：DatabaseName_TableDiff_YYYYMMDDHHMMSS.txt，比如：AdventureWorks2012_TableDiff_20171122230836.txt。