SQL SERVER compares the differences between tables and fields in two databases

  The database table fields and the Local database table fields on the centerline of the development process are present, and perhaps the table fields we added in the local database will be recorded in the SQL file.    But when the increase of the table and the name of the field will always appear through the gap, release is really a lot of issues when the release, then how to release the new version to ensure that the online database and the table field in the local database is unified, and below I say a more stupid way to ensure that two databases no differences. 1, the online database schema generation into SQL, the operation is as follows: Select a database on a line → task → generate script → next → select object window, select (script the entire database and all database objects) next → Set script Options window, Output Type tab, select "Save script to Special Fixed location "Click the Advanced" button, pop up the advanced scripting options, "Table/Try Options" and "General", in the "General Options" find "type of data to script" item select "Schema only" click the "OK" button, select the file save path in the filename option → next → next    Step → finish.    2. Create a new database locally (the database name cannot be the same as the database name that will be compared), and then execute the first step of the saved file in the newly created database. 3, began to compare two databases, execute the following SQL statement, the following SQL statement intfsimsnew represents a new database (online database, which is just the second step of the new database), Intfsims represents the old database (local database), the following SQL statement all Intfsimsnew Replace with your new database name, Intfsims Replace with your local database name 
  

--U table, p stored procedure, v view--intfsimsnew new library, intfsims old Library Select ntable = a.name, otable = B.namefrom intfsimsnew. SYSOBJECTS A left JOIN intfsims. SYSOBJECTS B on a.name = B.namewhere ISNULL (b.name, ') = ' and A.xtype = ' U ' UNION allselect ntable = b.name, otable = A.namefrom Intfsims. SYSOBJECTS A left JOIN intfsimsnew.  SYSOBJECTS B on a.name = B.namewhere ISNULL (b.name, ") = ' and A.xtype = ' U ' ORDER by 1, 2--compare differences for each table field in two databases select Table name a = case when ISNULL (A.tablename, ') <> "then A.tablename ELSE b.tablename END, field name a = a.fieldname, field name B = B. FIELDNAME, order = a.fieldsno, description = case when A.fieldtype <> b.fieldtype then ' type: ' + A.fieldtype + '--' + B.field              TYPE when A.fieldsno <> B.fieldsno then ' order: ' + str (A.FIELDSNO) + '--+ ' + str (B.FIELDSNO) When A.length <> b.length then ' length: ' + str (a.length) + '--' + str (b.length) when A.lensec <      > b.lensec Then ' decimal place: ' + str (a.lensec) + '--' + str (B.LENSEC)        When A.allownull <> b.allownull then ' Allow null values: ' + str (a.allownull) + '--+ ' + str (b.allownull) ENDFR        OM (SELECT TABLENAME = b.name, FIELDNAME = a.name, Fieldsno = a.colid, FieldType = C.name, LENGTH = a.length, lensec = a.xscale, Allownull = a.isnullable from intfsimsnew. syscolumns A left JOIN intfsimsnew. SYSOBJECTS B on a.id = b.ID left JOIN intfsimsnew. Systypes C on a.xusertype = c.xusertype WHERE b.xtype = ' U ') A full JOIN (SELECT TABLENAME =               B.name, FIELDNAME = a.name, Fieldsno = a.colid, FieldType = C.name, LENGTH = a.length, lensec = a.xscale, Allownull = a.isnullable from Intfsims. syscolumns A left JOIN intfsims. SYSOBJECTS B on a.id = b.ID left JOIN intfsims. Systypes C on a.xusertype = C.xusertyPE WHERE b.xtype = ' U ') B on a.tablename = b.tablename and A.fieldname = B.fieldnamewhere ISNULL (a.tab  Lename, ') = ' or ISNULL (b.tablename, ') = ' or A.fieldtype <> b.fieldtype OR a.fieldsno <> B.fieldsno or a.length <> b.length or a.lensec <> b.lensec or A.allownull <> b.allownullorder by 1, 4

　　

在开发过程中线上的数据库表字段和本地数据库表字段是存在的，也许我们在本地数据库中所增加的表字段都会有记录到SQL文件中，但当增加的表及字段名称较多时总会出现漏网之鱼，发布真是版本的时候回出现很多很多的问题，那么如何在发布新的版本时保证线上数据库与本地数据库中的表字段都是统一的了，下面我说一个比较笨的方法去保证两个数据库无差异。    1、将线上的数据库架构生成成为SQL，操作如下： 选择中某线上数据库 → 任务 → 生成脚本 → 下一步  → 选择对象窗口，选择（编写整个数据库及所有数据库对象的脚本）下一步  → 设置脚本编写选项窗口，输出类型选项卡中选择 “将脚本保存到特定的位置”  点击“高级”按钮，弹出 高级脚本编写选项，将出现“表/试图选项”及“常规”，在“常规选项中”找到“ 要编写脚本的数据的类型 ”项 选择 “仅限架构” 点击“确定”按钮，在文件名选项中选择文件保存的路径  →  下一步  →  下一步 → 完成 。    2、在本地新建一个数据库（数据库名称不能和将要对比的数据库名称一样），然后在新创建的数据库中执行第一个步保存的文件。    3、开始对比两个数据库，执行如下SQL语句，如下SQL语句中INTFSIMSNEW 表示新数据库（线上数据库，也就是刚刚第二步新建的数据库），INTFSIMS 表示旧数据库（本地数据库），将如下SQL语句中所有 INTFSIMSNEW 替换成你新建的数据库名称，INTFSIMS 替换成你本地的数据库名称

--U-table, p-stored procedure, V-View--intfsimsnew new library, intfsims old librarySELECTntable = a.name, otable = B.name fromIntfsimsnew. SYSOBJECTS A Left JOINIntfsims. SYSOBJECTS B onA.name = B.nameWHEREISNULL (B.name,"') ="'   andA.xtype =' U 'UNION  AllSELECTntable = b.name, otable = A.name fromIntfsims. SYSOBJECTS A Left JOINIntfsimsnew. SYSOBJECTS B onA.name = B.nameWHEREISNULL (B.name,"') ="'   andA.xtype =' U 'ORDER  by 1,2--Compare the differences of each table field in two databasesSELECTTable name A = Case  whenISNULL (A.tablename,"') <>"'  ThenA.tablenameELSEB.tablenameEND, field name A = a.fieldname, field name B = b.fieldname, order = a.fieldsno, Description = Case  whenA.fieldtype <> B.fieldtype Then ' type: '+ A.fieldtype +' --'+ B.fieldtype whenA.fieldsno <> B.fieldsno Then ' Order: '+ STR (A.FIELDSNO) +' --'+ STR (B.FIELDSNO) whenA.length <> B.length Then ' Length: '+ STR (a.length) +' --'+ STR (b.length) whenA.lensec <> b.lensec Then ' decimal digits: '+ STR (A.LENSEC) +' --'+ STR (B.LENSEC) whenA.allownull <> B.allownull Then ' Allow null value: '+ STR (a.allownull) +' --'+ STR (b.allownull)END from(SELECTTABLENAME = b.name, FIELDNAME = a.name, Fieldsno = a.colid, FieldType = c.name, LENGTH = A.len GTH, lensec = a.xscale, Allownull = a.isnullable fromIntfsimsnew. syscolumns A Left JOINIntfsimsnew. SYSOBJECTS B ona.ID = b.ID Left JOINIntfsimsnew. Systypes C onA.xusertype = C.xusertypeWHEREB.xtype =' U ') A Full JOIN(SELECTTABLENAME = b.name, FIELDNAME = a.name, Fieldsno = a.colid, FieldType = C.name, LENGTH = a.length, lensec = a.xscale, Allownull = a.isnullable fromIntfsims. syscolumns A Left JOINIntfsims. SYSOBJECTS B ona.ID = b.ID Left JOINIntfsims. Systypes C onA.xusertype = C.xusertypeWHEREB.xtype =' U ') B onA.tablename = B.tablename andA.fieldname = B.fieldnameWHEREISNULL (A.tablename,"') ="'  ORISNULL (B.tablename,"') ="'  ORA.fieldtype <> B.fieldtypeORA.fieldsno <> B.fieldsnoORA.length <> B.lengthORA.lensec <> b.lensecORA.allownull <> B.allownullORDER  by 1,4

SQL SERVER compares the differences between tables and fields in two databases