正文
sqlserver tablediff 实用工具
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
tablediff 是sqlserver自带的实用工具
sqlserver 2012 在110目录下，sqlserver2008在100目录下
官方参考文档如下：
https://docs.microsoft.com/en-us/sql/tools/tablediff-utility?view=sql-server-2017
既可以做一定级别的数据kpi验证，也可以用于 对比 测试环境和生产环境的 数据库表数据差异，并生成收敛差异的批量sql。
具体功能可以参考上述官方文档。
下面实测具体用途。
数据收敛首先要明确数据应该是什么样的，比如我测试环境需要同步生产环境数据，那么我需要收敛的是测试环境缺失的数据。
在这种情况下，我们需要把 源端定义为生产环境数据库，目标端定义为测试环境数据库。
源端数据库执行：
tablediff -sourceserver "生产环境数据库IP地址" -sourcedatabase "生产环境数据库" -sourceschema "生产环境架构名称"
-sourcetable "生产环境表" -sourceuser "生产环境登录名称"
-sourcepassword "生产环境密码" -destinationserver "测试环境数据库ip地址"
-destinationdatabase "测试环境数据库名称" -destinationschema "测试环境架构名称"
-destinationtable "测试环境的mapping表" -destinationuser "测试环境登录名" -destinationpassword "测试环境数据库用户密码"
-f "d:\tools\V_SYS_MESSAGES_Diff.sql"
这样就生成了收敛的sql，具体语句为insert。
坑1： tablediff 工具通过比较行内容进行收敛，是存在一定问题的，比如 “NULL” 值 是不能进行比较的，大对象字段很难比较，这需要将表进行视图转换：
过滤掉null，xtype等特殊字段。当然，表中没有这些字段或者无null更好。
源端执行：
declare @table_name nvarchar(100)
set @table_name='SYS_MESSAGES'
if exists (select 1 from sys.tables where name=@table_name)
begin
declare @table_info table
(id int identity(1,1) not null,info nvarchar(300))
insert into @table_info
select 'create view V_'+@table_name+'_Diff'
insert into @table_info
select 'as'
insert into @table_info
select 'select '
insert into @table_info
select case when isnullable=0 then tablename+','
else tablename+','+default_value+') as '+name+',' end
from (
select a.isnullable,a.name,b.xtype,
case when a.isnullable =0 then ' ['+a.name+']' else ' ISNULL('+a.name end as tablename,
case when b.xtype in (48,52,56,127) then '-100' --tinyint\smallint\int\bigint
when b.xtype in (59,60,62,106,108) then '1.01'--real\money\float\decimal\numeric
when b.xtype=40 then '''1900-01-01''' --date
when b.xtype=41 then '''01:00:01''' --time
when b.xtype=58 then '''1900-01-01 01:01:01''' --smalldatetime
when b.xtype=61 then '''1900-01-01 01:01:01.001''' --datetime
else '''@#''' end as default_value
from syscolumns a,systypes b
where a.id = OBJECT_ID(@table_name) and a.xtype = b.xusertype
)a where xtype not in(34,35,99,165,173,189,241)--xml\timestamp\binary\varbinary\ntext\text\image update @table_info set info=SUBSTRING(info,1,len(info)-1) where id=
(select MAX(id) from @table_info) insert into @table_info values('from dbo.['+@table_name+'] with(nolock)') select info from @table_info
end
else
print('Can not find the table '+@table_name)
生成视图的sql
create view V_SYS_MESSAGES_Diff
as
select
[MSG_ID],
ISNULL(MSG_TIME,'@#') as MSG_TIME,
ISNULL(MSG_TYPE,'@#') as MSG_TYPE,
ISNULL(MSG_CONTENT,'@#') as MSG_CONTENT,
ISNULL(MSG_USER,'@#') as MSG_USER,
ISNULL(MSG_MARK1,'@#') as MSG_MARK1,
ISNULL(MSG_MARK2,'@#') as MSG_MARK2,
ISNULL(MSG_MARK3,'@#') as MSG_MARK3,
ISNULL(PROCESS_FLAG,-100) as PROCESS_FLAG
from dbo.[SYS_MESSAGES] with(nolock)
目标端也重复上述操作。然后再利用tablediff工具进行对比收敛。
