比较2个数据库的差异

浏览数：17 / 时间：2015年06月12日

对已经运行的系统进行大规模模块开发之后，对数据库和程序修改比较大。

如果开始开发的时候没有严格要求，或者人员没有按照要求做，或者人员离职交接不合格，很容易造成文档不完整，必须比较两个数据库的差别。

下面的SQL比较出两个数据库中表结构有差的地方。

SELECT  obj.name AS TableName, col.name AS ColName, col.xtype, 
      col.length
into #tmp1
FROM eShipping_New_QA_User_1_BackUp.dbo.syscolumns col INNER JOIN
      eShipping_New_QA_User_1_BackUp.dbo.sysobjects obj ON col.id = obj.id
ORDER BY obj.name

SELECT  obj.name AS TableName, col.name AS ColName, col.xtype, 
      col.length
into #tmp2
FROM eShipping_20140805.dbo.syscolumns col INNER JOIN
      eShipping_20140805.dbo.sysobjects obj ON col.id = obj.id
ORDER BY obj.name

select  tablename,colname,0 as xtypeold,0 as lengthold,0 as xtypenew,0 as lengthnew
into #tmp3
from (select tablename,colname from #tmp1 union select tablename,colname from #tmp2)
b

update #tmp3 set xtypeold=#tmp1.xtype,lengthold=#tmp1.length  from #tmp1 where #tmp3.tablename=#tmp1.tablename and
#tmp3.colname=#tmp1.colname

update #tmp3 set xtypenew=#tmp2.xtype,lengthnew=#tmp2.length  from #tmp2 where #tmp3.tablename=#tmp2.tablename and
#tmp3.colname=#tmp2.colname

select * from #tmp3
where xtypeold<>xtypenew or lengthold<>lengthnew 

drop table #tmp1
drop table #tmp2
drop table #tmp3