对已经运行的系统进行大规模模块开发之后,对数据库和程序修改比较大。
如果开始开发的时候没有严格要求,或者人员没有按照要求做,或者人员离职交接不合格,很容易造成文档不完整,必须比较两个数据库的差别。
下面的SQL比较出两个数据库中表结构有差的地方。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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