SQLServer 不同数据库进行表结构比较

 

1.在目标数据中中执行以下代码，生成表结构比对Sql

select
' if exists(
 select 1 from  dbo.syscolumns a 
inner join  sys.tables t on t.object_id = a.id 
inner join systypes c on c.xusertype = a.xusertype
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sysobjects t2 on t.object_id=t2.id
 where t2.xtype=''u'' and t2.name='''+t2.name+''' and a.name='''+a.name+'''
)begin print 1 end else begin select '''+t2.name+''' as TableName,'''+a.name+''' as FieldName,'''+c.name+''' as FieldType, ''Lack Field'' as Status end'
from  dbo.syscolumns a 
inner join  sys.tables t on t.object_id = a.id 
inner join systypes c on c.xusertype = a.xusertype
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sysobjects t2 on t.object_id=t2.id
where t2.xtype='u'

生成界面如下：

 

 

2.  Copy 查询结果，在目标数据库中执行。

 查询结果界面如下：