After the large-scale module development of the already running system, the database and the program changes are relatively large.
If you start to develop without strict requirements, or personnel do not follow the requirements, or personnel turnover unqualified, it is easy to cause the document is incomplete, you must compare two database differences.
The following SQL compares the differences in the table structure in two databases.
SELECTObj.name asTableName, Col.name ascolname, Col.xtype, Col.length into#tmp1 fromEShipping_New_QA_User_1_BackUp.dbo.syscolumns ColINNER JOINeShipping_New_QA_User_1_BackUp.dbo.sysobjects obj onCol.id=obj.idORDER byObj.nameSELECTObj.name asTableName, Col.name ascolname, Col.xtype, Col.length into#tmp2 fromEShipping_20140805.dbo.syscolumns ColINNER JOINeShipping_20140805.dbo.sysobjects obj onCol.id=obj.idORDER byObj.nameSelectTablename,colname,0 asXtypeold,0 asLengthold,0 asXtypenew,0 aslengthnew into#tmp3 from(SelectTablename,colname from#tmp1Union SelectTablename,colname from#tmp2) bUpdate#tmp3SetXtypeold=#tmp1. Xtype,lengthold=#tmp1. Length from#tmp1where#tmp3. TableName=#tmp1. TableName and#tmp3. ColName=#tmp1. ColNameUpdate#tmp3SetXtypenew=#tmp2. xtype,lengthnew=#tmp2. Length from#tmp2where#tmp3. TableName=#tmp2. TableName and#tmp3. ColName=#tmp2. ColNameSelect * from#tmp3whereXtypeold<>XtypeneworLengthold<>lengthnewDrop Table#tmp1Drop Table#tmp2Drop Table#tmp3
View Code