tableDiff的用法

 
USE tempdb
GO

--创建测试表
CREATE TABLE Tmp
(
 ID int PRIMARY KEY,
 Account varchar(32),
 CharName varchar(32)
)
GO

--插入测试数据
INSERT Tmp
SELECT 1,'account1','CharName1'
UNION ALL SELECT 2,'account2','CharName1'
UNION ALL SELECT 3,'account3','CharName1'
GO

--创建Check表
CREATE TABLE Tmp_Check
(
 ID int PRIMARY KEY,
 Account varchar(32),
 CharName varchar(32)
)
GO
INSERT Tmp_Check
SELECT 1,'account11','CharName11'


--用TableDiff比对
EXEC xp_cmdshell 'tablediff -sourceserver "localhost" -sourcedatabase "tempdb" -sourceschema "dbo" -sourcetable "Tmp" -sourceuser "sa" -sourcepassword "pwd" -destinationserver "localhost" -destinationdatabase "tempdb" -destinationschema "dbo" -destinationtable "Tmp_Check" -destinationuser "sa" -destinationpassword "pwd" -f "c:\Test.sql"'
GO

--读取Test.sql
DECLARE @tmp Tmp(SqlStr varchar(max))
INSERT @tmp
exec xp_cmdshell'type c:\test.sql'
SELECT 
 SqlStr 
FROM @tmp
WHERE SqlStr is not null and CHARINDEX('--',sqlstr) = 0
 
--删除测试表和文件
DROP TABLE Tmp
DROP TABLE Tmp_Check
EXEC xp_cmdshell 'del c:\Test.sql'
GO