/ * - Compare the table structure difference between the two databases - Zou Jian 2003.9 (Reference Please keep this information) - * // * - Call example EXEC P_COMPARESTRUCTURE 'XZKH_MODEL', 'XZKH_NEW' - * /
if exists (select * from dbo.sysobjects where id = object_id (N '[dbo]. [p_comparestructure]') and OBJECTPROPERTY (id, N'IsProcedure ') = 1) drop procedure [dbo]. [p_comparestructure] GO
Create Proc P_compareStructure @ dbname1 varchar (250), - To compare the database name 1 @ dbname2 varchar (250) - Database name 2ASCREATE TABLE # TB1 (Table 1 varchar (250), Field name VARCHAR (250) , Serial number INT, Int, primary key bit, type varchar (250), occupying bytes INT, length INT, decimal number INT, allows empty bits, default VARCHAR (500), field Description VARCHAR (500))
Create Table # TB2 (Table Name 2 VARCHAR (250), Field Name VARCHAR (250), Sequence INT, INT, Primary Key Bit, Type VARCHAR (250), Used bytes INT, Length INT, Quadrigital Int, Allow Empty Bit, Default VARCHAR (500), Field Description VARCHAR (500))
- Get the structure of the database 1 EXEC ('INSERT INTO # tb1 select table name = D.Name, field name = a.name, serial number = a.colid, logo = case when a.status = 0x80 Then 1 else 0 End, primary key = case when exists (SELECT 1 FROM ' @ dbname1 ' .. sysobjects where xtype = '' PK '' and name in (SELECT name FROM ' @ dbname1 ' .. sysindexes WHERE indid in (SELECT indid FROM ' @ DBNAME1 '.. sysindexkeys where id = a.id and color = a.colid)) Then 1 else 0 end, type = B.Name, the number of bytes = a.length, length = A.PREC, decumeration = a.scale, allowed empty = a.isnullable, default = isnull (e.text, '' ''), field description = ISNULL (g. [value], '' '') from ' @ dbname1 '.. syscolumns a left join' @ dbname1 '.. systemypes b on a.xtype = B.XUSERTYPE INNER JOIN' @ dbname1 '.. sysobjects d on a.id = D.ID and D.XTYPE = '' U '' and D.Name <> 'DTPROPERTIES' '' LEFT JOIN ' @ dbname1 ' .. syscomments e on a.cdefault = E.ID LEFT JOIN ' @ dbname1 ' .. sysproperties g on A. ID = g.id and a.colid = g.smallid order by a.id, a.colorder ')
- Get the structure of the database 2 EXEC ('INSERT INTO # TB2 SELECT table name = D.Name, field name = a.name, serial number = a.colid, logo = case when a.status = 0x80 Then 1 else 0 End, primary key = case when exists (SELECT 1 FROM ' @ dbname2 ' .. sysobjects where xtype = '' PK '' and name in (SELECT name FROM ' @ dbname2 ' .. sysindexes WHERE indid in (SELECT indid FROM ' @ DBNAME2 '.. sysindexkeys where id = a.id and color = a.colid)) Then 1 else 0 end, type = B.Name, accounting number = a.Length, length = a.PREC, decumeration = a.scale, allowed empty = a.isnullable, default = isnull (e.text, '' ''), field description = ISNULL (g. [value], '' '') from ' @ dbname2 '.. syscolumns a left join' @ dbname2 '.. systypes b on a.xtype = B.XUSERTYPE INNER JOIN' @ dbname2 '.. sysobjects d on a.id = D.ID and D.XTYPE = '' U '' and d.name <> 'dtproperties' '' l ' @ dbname2 ' .. syscomments e on a.cdefault = E.ID Left Join ' @ dbname2 ' .. sysproperties g on A.ID = g.ID and a.colid = g.smallid order by a.id, a.colorder ') - and not exists (SELECT 1 from # TB2 WHERE table 2 = a. Table name 1) SELECT comparison result = Case WHEN a. Name 1 is Null and b. serial number = 1 Then 'library 1 lack table:' b. Table name 2 WHEN B. Table name 2 is Null and a. serial number = 1 t HEN 'library 2 lack table:' a. Name 1 WHEN a. Field name is null and exists (SELECT 1 from # TB1 WHERE table name 1 = b. Table name 2) The 'library 1 [' b. table Name 2 '] Missing Field:' b. Field Name WHEN B. Field Name Is Null and Exists (SELECT 1 from # TB2 WHERE Table Name 2 = a. Table Name 1) Then 'Library 2 [' a. Table Name 1 ] Missing field: ' a. Field name when a. Identifier <> b. Identifier THEN' ID Different '
