Handmade is compared to both time and labor, so I wrote SQL compared to two database structural differences, the comparison content is: table field (type, whether to allow empty, identification, field order, default, default name, formula, sort rule ) Index (whether the primary key, whether the index is aggregated, whether the unique index, index name) view stored procedure trigger function Check constraint foreign key constraints (primary key table, primary key column, foreign key name) Interested friends, please help test, perhaps future Somomplace is useful to you, what else needs to increase, thank you
// --------------------------------------------
Declare @ db1 varchar (50), @ db2 varchar (50) SELECT @ db1 = 'test', @ db2 = 'test1' - Name of the Database to Compare - Author: Pbsql - Date: 2005-12-12 --Compare Database Structure: - EXIST INE DATABASE BUT NOT IN ANOTHER: - 1.Table, 2.Column, 3.index, 4.ew, 5.procedure, 6.trigger, 7.Function - 8 . CHECK CONSTRAINT, 9.FOREIGN KEY - Column: - 1.Data Type, 2.Allow Nulls, 3.Identity, 4.order, - 5.default value, 6.default Name, 7.Formula, 8. COLLATION - INDEX: - 1.isClustered, 2.isunique, 3.isprimarykey, 4.index name - foreign key: - 1.The Reference Table, 2.Column of the reference Table, - 3.Foreign Key Nameset NoCount OnSet ANSI_NULLS OFF
Create Table #difference (ID Int Id Id Id Intity (1), ObjectType Varchar (50), ObjectName Nvarchar (400), Desc_Difference NVARCHAR (3500)) Create Table #tbname (ID Int Id Id Id Id Id Id Id Id Id Id Id Id Id Id Id Id Int Id "
--all user table exist in @ db1, and also exist in @ db2exec ('INSERT #TBName (TBNAME) select name from' @db1 .dbo.sysobjects t where xtype =' 'u' 'and exists (SELECT 1 From' @db2 .dbo.sysObjects where xtype = t.xtype and name = t.name) Order by name '
--objects exist in one database, but not exist in anotherexec ( 'insert #difference (desc_difference, objecttype, objectname) select' 'In' @ db1 ', but not in' @ db2 '' ', case when xtype = N''' '' '' '' '' '' '' '' '' '' '' '' '' '' 'When Xtype = N'' '' Ten '' Stored Procedure '' When Xtype = N'' '' '' 'Trigger' 'WHEN XTYPE IN (N'' Fn ', N'' ', N'' '') Then '' Function 'End, Name from' @db1 .dbo .sySObjects T where xtype in (N'', N'', N'', N'', N'', N'', N'', N '' TF '') And not exists (SELECT 1 from' @db2 .dbo.sysobjects where xtype = t.xtype and name = t.name) Union allselect '' in ' @ db2 ', but not in ' @ DB1 '', Case When Xtype = N''' '' '' '' '' '' '' '' '' '' '' '' '' '' '' WHEN Xtype = N''' '' Then '' Stored Procedure '' When Xtype = N''' '' Ten '' Trigger '' When Xtype in (N''Fn', N''' ', N'' '') Then '' Function ' End, Name from ' @ db2 '. DBO.SYSOBJECTS T where type in (n '
'U' ', N'' ', N'' ', N''tr' ', N'' '', N''' ', N'' '') And not exists (SELECT 1 from' @db1 .dbo.sysobjects where xtype = t.xtype and name = t.name) ') // 2 Page
- Continued --all columns and column propertycreate table # columns1 (id int identity (1,1), tbname sysname, colname sysname, xusertype smallint, length smallint, defaultname varchar (100), defaulttext nvarchar (1000), colorder smallint , prec smallint, scale int, computedformula nvarchar (2000), isnullable int, collation nvarchar (128), isidentity int, identseed int, identincr int) create table # columns2 (id int identity (1,1), tbname sysname, colname sysname , xusertype smallint, length smallint, defaultname varchar (100), defaulttext nvarchar (1000), colorder smallint, prec smallint, scale int, computedformula nvarchar (2000), isnullable int, collation nvarchar (128), isidentity int, identseed int, identincr int) exec ( 'use' @ db1 'insert # columns1 (tbname, colname, xusertype, length, defaultname, defaulttext, colorder, prec, scale, computedformula, isnullable, collation, isidentity, identseed, identincr) select a.name, B.Name, B.XUSERTYPE, B.LENGTH, (Select X.Name from' @db1 .dbo.sysObjects x, ' @ db1 '. dbo.syscolumns y where x. ID = y.cdefault and y.id = a.id and y.Name = B.Name), c. [text], b.colorder, isnull (b.prec, 0), isnull (b.scale, 0) , D. [Text], B.isnullable, B. [Collation], ColumnProperty (B.ID, B.Name, '' isidentity '), Ident_seed (a.name), Ident_inCr (a.name) from' @ db1 '. dbo.sysObjects a inner Join' @db1 .dbo.syscolumns b on a.id = B.ID Left join' @db1 .dbo.syscomments c on b.cdefault = C.ID Left Join ' @db1 .dbo.syscomments D on b.id = D.ID and b.iscomputed = 1 where a.xtype =' 'u' 'and exists (SELECT 1 from' @ db2 "
.dbo.sysObjects e, ' @ db2 '. dbo.syscolumns f Where E.Id = f.id and a.name = E.NAME AND B.NAME = f.name) Order by a.name, B.ColOrderuse ' @ db2 ' insert # columns2 (tbname, colname, xusertype, length, defaultname, defaulttext, colorder, prec, scale, computedformula, isnullable, collation, isidentity, identseed, identincr) select a.name, b.name, b. XUSERTYPE, B.LENGTH, (Select X.Name from' @db2 .dbo.sysobjects x, ' @ db2 '. dbo.syscolumns y where x.id = y.cdefault and y.id = A.ID AND Y.Name = B.Name), C. [Text], B.ColORder, Isnull (B.PREC, 0), ISNULL (B.scale, 0), D. [Text], B.Inullable, B. COLLATION], ColumnProperty (B.ID, B.Name, '' IsIdentity '), Ident_seed (a.name), Ident_InCr (a.name) from' @db2 .dbo.sysobjects a inner Join' @ db2 '.dbo.syscolumns b on a.id = B.ID Left Join' @db2 .dbo.syscomments c on b.cdefault = c.id left join' @db2 .dbo.syscomments D on B.ID = D.ID and b.iscomputed = 1 where a.xtype = '' u '' and exists (SELECT 1 from' @db1 .dbo.sysobjects e, ' @ db1 '. dbo.sysc Olumns f Where e.id = f.id and a.name = E.NAME AND B.NAME = f.name) Order by a.name, b.colorder ')
--column exist in @ db1, but not exist in @ db2exec ( 'insert #difference (desc_difference, objecttype, objectname) select desc_difference, objecttype, objectnamefrom (select top 100 percent a.name, b.colorder, desc_difference =' 'In ' @ db1 ' ', but not in' @ db2 '..' ' a.name, ObjectType =' 'column', Objectname = B.NAME FROM ' @ DB1 '. dbo.sysObjects a,' @ db1 '. dbo.syscolumns b where a.xtype =' 'u' 'and a.id = B.ID and exists (SELECT 1 from # colorns1 where tbname = a.name ) And not exists (SELECT 1 from # Column1 where tbname = a.name and color = B.Name) Order by a.name, b.colorder) T ') - Column EXIST IN @ db2, but not exist in @ db1exec ( 'insert #difference (desc_difference, objecttype, objectname) select desc_difference, objecttype, objectnamefrom (select top 100 percent a.name, b.colorder, desc_difference =' 'In' @ db2 '..' ' a.name ' ', but not in' @ db1 '.' ' a.name, ObjectType =' 'column', objectName = B.Name from' @db2 @dbo.sysObjects a, ' @ db2 '. DBO.SYSCOLUMNS B WH ERE a.XTYPE = '' u '' and a.id = B.ID and exists (SELECT 1 from # colorns2 where tbname = a.name) and not exists (SELECT 1 from # colorns1 where tbname = a.name and colname = B.Name) Order by a.name, b.colorder) T ')
--column data type is differentexec ( 'insert #difference (desc_difference, objecttype, objectname) select' 'Data Type:' ' t1.name case when t1.name in (' 'binary' ',' 'varbinary' ' , '' char '', '' '' '' ') Then' '(' ' Cast (A.Length As Varchar (10)) ' '' 'When T1.NAME IN (' Nchar ',' 'nvarchar' ') Then' '(' ' cast (a.length / 2 as varchar (10)) ' ')' 'When T1.Name in (' Decimal '', '' Numeric ') THEN '' ('' cast (A.PREC AS VARCHAR (10)) '', '' Cast (A.Scale As Varchar (10)) '' 'ELSE' '' End '' - - '' '' ' @ db1 ' '' '', '' T2.Name Case When T2.Name in ('' binary '', '' Varbinary '', '' char ',' 'VARCHAR' ') THEN' '(' ' Cast (B.Length As Varchar (10)) ' '' '' ',' Nchar '', 'Nvarchar' ') Then' ' ('' CAST (B.Length / 2 As Varchar (10)) '') '' 'WHEN T2.NAME IN (' 'Decimal' '') Then '' ('' Cast B.PR EC As Varchar (10)) '', '' Cast (B.scale As Varchar (10)) ''
) '' Else '' '' end '' - '' '' ' @ db2 ' '', '' column '', a.tbname ''. '' A.colname from # Columns1 a inner join # colorns2 b on a.tbname = b.tbname and a.colname = b.colname left join' @db1 .dbo.systypes t1 on a.xUsertype = t1.xUsertype left join' @db2 .dbo.sySystypes T2 on b.XUSERTYPE = T2.XUSERTYPE WHERE A.XUSERTYPE <> B.XUSERTYPE OR A.LENGTH <> B.LENGTH OR A.PREC <> b.prec or a.scale <> b.scale ') - Column allow nulls is differentinsert #difference (desc_difference, objecttype, objectname) select 'Allow Nulls:' case a.isnullable when 0 then 'not' else '' end 'null -' @ db1 ',' case b.isnullable When 0 THEN 'NOT' ELSE 'END ' NULL - ' @ DB2,' Column ', A.TBNAME '. ' a.colname from # colorns1 a inner join # Column2 b on a.tbname = b.tbname And a.colname = b.colname where a.isnullable <> b.isnullable ---- // ---- 3 Page
- Continued --column identity is differentinsert #difference (desc_difference, objecttype, objectname) select 'Identity:' case a.isidentity when 1 then 'identity (' cast (a.identseed as varchar (10)) ' , ' Cast (A.IDENTINCR AS VARCHAR (10)) ') 'Else' NO Identity 'End ' - ' @ db1 ', ' Case B.isidentity When 1 Then' Identity (' Cast B.IDENTSEED AS VARCHAR (10)) ',' Cast (B.IDENTINCR AS VARCHAR (10)) ')' Else 'NO Identity' End '-' @ DB2, 'Column', A. TBNAME '.' a.colname from # Columns1 a inner Join # Column2 b on a.tbname = b.tbname and a.colname = b.colname where a.isidentity <> B.isidentity or A.IdentSeed <> B. identseed or a.identincr <> b.identincr - column order is differentinsert #difference (desc_difference, objecttype, objectname) select 'column Order:' cast (a.colorder as varchar (10)) '-' @ DB1 ',' CAST (B.ColORDER AS VARCHAR (10)) '-' @ DB2, 'Column', a.tbname '.' a.colname from #colum NS1 a inner Join # Column2 b on a.tbname = b.tbname and a.colname = b.colname where a.colorder <> B.ColORder
--column default value is differentinsert #difference (desc_difference, objecttype, objectname) select 'Default Value:' isnull (a.defaulttext, 'no default') 'in' @ db1 ',' isnull (b.defaulttext , 'No Default') 'IN' @ DB2, 'Column', a.tbname '.' a.colname from # Columns1 A Inner Join # Column2 b on a.tbname = b.tbname and a.colname = b.colname where a.defaulttext is not null and b.defaulttext is not null and a.defaulttext <> b.defaulttext or a.defaulttext is not null and b.defaulttext is null or a.defaulttext is null and b.defaulttext is not null - column default name is differentinsert #difference (desc_difference, objecttype, objectname) select 'default name:' isnull (a.defaultname, 'no default') 'in' @ db1 ',' isnull (b .defaultname, 'no default') 'in' @ db2, 'colorn', a.tbname '.' a.colname from # Columns1 a inner join # Column2 b on a.tbname = b.tbname and a. ColName = B.Colname where a.defaulttext is not null and b.defaulttext is not null and a.defaultna ME <> b.defaultname
--column formula is differentinsert #difference (desc_difference, objecttype, objectname) select 'Formula:' isnull (a.computedformula, 'no formula') 'in' @ db1 ',' isnull (b.computedformula, ' NO formula ') ' IN ' @ DB2,' Column ', a.tbname '. ' a.colname from # colorns1 a inner join # Column2 b on a.tbname = b.tbname and a.colname = b. colname where a.computedformula is not null and b.computedformula is not null and a.computedformula <> b.computedformula or a.computedformula is not null and b.computedformula is null or a.computedformula is null and b.computedformula is not null --column collation is differentinsert #difference (desc_difference, objecttype, objectname) select 'collation:' isnull (a.collation, 'no collation') 'in' @ db1 ',' isnull (b.collation, ' NO collation ') ' in ' @ db2,' colorn ', a.tbname '. ' a.colname from # Columns1 a inner join # Column2 b on a.tbname = b.tbname and a.colname = b. Colname where a.xUsertype = B.XUSERTYPE AND (A.COLLLATION IS NOT N ULL AND B.COLLATION IS NOT NULL AND A.COLLATION <> B.Collation or a.collation is not null and b.collation is null or a.collation is null and b.collation is not null)
--Compare indexcreate table # indexes1 (tbname sysname, indexname sysname, colname sysname, keyno smallint, isunique int, isclustered int, indexcol nvarchar (1000), isprimarykey bit) create table # indexes2 (tbname sysname, indexname sysname, colname sysname, keyno smallint, isunique int, isclustered int, indexcol nvarchar (1000), isprimarykey bit) exec ( 'use' @ db1 'declare @indexcol nvarchar (1000), @ indexname nvarchar (128) insert # indexes1 (tbname, indexname, colname, keyno, isunique, isclustered, isprimarykey) select tbname = c.name, indexname = b.name, colname = d.name, a.keyno, isunique = INDEXPROPERTY (a.id, b.name, '' isUnique ''), isclustered = INDEXPROPERTY (a.id, b.name, '' IsClustered ''), isprimarykey = case when exists (select 1 from sysobjects where xtype = '' PK '' and name = b.name) then 1 else 0 end from Sysindexkeys a, sysindexes b, sysobjects c, syscolumns d where A.Id = B.ID and a.indid = B.Idid and A.ID = C.ID and C.ID = D.ID and a.colid = D. Colid a Nd c.xtype = '' u '' and indexproperty (A.ID, B.Name, '' isautostatistics') = 0 and exists (SELECT 1 from #tbname where tbname = c.name) Order by TBName, IndexName, keynoselect @indexcol = '' '', @ indexname = '' '' update # indexes1 set @ indexcol = case when @indexname <> indexname then colname else @indexcol '' '' colname end, indexcol = @ indexcol, @ IndexName =
indexnamedelete from # indexes1 where exists (select 1 from # indexes1 t where # indexes1.tbname = t.tbname and # indexes1.indexname = t.indexname and # indexes1.keyno
t.indexname and # indexes2.keyno
- Continued --index is different on isuniqueinsert #difference (desc_difference, objecttype, objectname) select case a.isunique when 1 then 'Unique' else 'Not unique' end '-' @ db1 ',' case b .isunique when 1 Then 'unique' Else 'not unique' end '-' @ DB2, 'Index', 'INDEX ON' A.TBNAME '(' a.indexcol ')' from # indexes1 a, # indexes2 b where a.tbname = b.tbname and a.indexcol = b.indexcol and a.isunique <> b.isunique - index is different on isprimarykeyinsert #difference (desc_difference, objecttype, objectname) select case a.isprimarykey when 1 THEN 'PRIMARY Key' Else 'NOT Primary Key' END '-' @ db1 ' , 'Index', 'index on' a.tbname '(' a.indexcol ')' from # indexes1 a, # indexes2 b where a.tbname = B.TBName and A.indexcol = B.Indexcol and a. IsprimaryKey <> B.isprimaryKey - Index Name is DifferentInsert #difference (desc_difference, objecttype, objectname) Select 'Index Name is Different:' A .INDEXNAME '-' @ db1 ',' b.indexname '- ' @db2,' index ',' Index ON ' A.TBNAME ' (' a.indexcol ') 'from # indexes1 a , # indexes2 b where a.tbname = b.tbname and a.indexcol = B.indexcol and a.indexname <> B.Indexname
--Check exist in @ db1, but not exist in @ db2exec ( 'insert #difference (desc_difference, objecttype, objectname) select' 'In' @ db1 ', but not in' @ db2 '' ',' 'Check ', name from' @db1 .dbo.sysobjects t where xtype =' 'c' 'and not exists (select 1 from' @db2 .dbo.sysObjects where xtype =' 'c' 'and name = T.Name) ') - Check EXIST IN @ db2, but not exist in @ db1exec (' INSERT #diference (desc_diference) select '' IN ' @ db2 ', but not in ' @ db1 ' '', '' Check '', name from' @db2 .dbo.sysobjects t where xtype = '' c '' and not exists (SELECT 1 from' @db1 .dbo.sysObjects where xtype = '" C '' and name = t.name) ')
--Compare check constraintscreate table # check1 (tbname sysname, checkname sysname, checktext nvarchar (3500)) create table # check2 (tbname sysname, checkname sysname, checktext nvarchar (3500)) exec ( 'insert # check1 (tbname, checkname, checktext Select TBName = B.Name, CheckName = a.Name, checktext = c. [text] from' @db1 .dbo.sysobjects a, ' @db1 .dbo.sysobjects b,' @ db1 '. DBO.SYSCOMMENTS C where a.XTYPE = '' c '' and a.id = C.ID and B.ID = a.parent_obj and exists (SELECT 1 from' @db2 .dbo.sysobjects where xtype = '" C '' and name = a.name) ') EXEC (' INSERT # check2 (tbname, checkname, checktext) select tbname = B.Name, checkname = a.name, checktext = c. [Text] from ' @ db2 '.dbo.sysObjects a,' @db2 .dbo.sysObjects b, ' @db2 .dbo.syscomments c where a.xtype =' 'c' 'and a.id = C.ID and B.ID = a.parent_obj and exists (select 1 from' @db1 '.dbo.sysobjects where xtype = '' C '' and name = a.name) ') - Check constraint text is differentexec (' insert #difference (desc_difference , Objectty PE, ObjectName) SELECT '' CHECK '' A.CHECKNAME '' ON TABLE '' A.TBNAME '' ',' '', '' ',' '', A.Checkname from # check1 a, # check2 b Where a .tbname = b.tbname and a.checkname = B.checkname and a.checktext <> b.checktext ') - //5 page ------------------- -----------
- Continued --Compare foreign key constraintcreate table # fk1 (fkname sysname, fktbname sysname, pktbname sysname, fkcolumns nvarchar (1800), pkcolumns nvarchar (1800)) create table # fk2 (fkname sysname, fktbname sysname, pktbname sysname, fkcolumns nvarchar (1800), pkcolumns nvarchar (1800)) exec ( 'use' @ db1 'insert # fk1 (fkname, fktbname, pktbname, fkcolumns, pkcolumns) select fkname = b.name, fktbname = c.name, pktbname = d .name, fkcolumns = isnull (Select Name from syscolumns where id = c.id and color = a.fkey1), '' ') isnull ((select', '' name from syscolumns where id = c. ID and color = a.fkey2), '' ') isnull (Select', '' name from syscolumns where id = c.id and color = a.fkey3), '' ') isnull (select ',' name from syscolumns where id = c.id and color = a.fkey4), '' ') isnull ((select', '' name from syscolumns where id = C.ID And color = a.fkey5), '' ') isnull (SELECT', '' NAME FROM Syscolumns where id = c.id and color = a.fkey6), '' ') isnull ((select', '' name from syscolumns where id = C.ID and color = a.fkey7), '' ') Isnull ((select', '' name from syscolumns where id = c.id and color = a.fkey8), '' ') isnull ((select', ''
Name from syscolumns where id = C.ID and color = a.fkey9), '' ') isnull (select', '' name from syscolumns where id = c.id and color = a.fkey10), '' ') Isnull (Select', '' Name from syscolumns where id = c.id and color = a.fkey11), '' ') isnull ((select', '' Name From syscolumns where id = c.id and color = a.fkey12), '' ') isnull (select', '' name from syscolumns where id = C.ID and color = a.fkey13), ' '') Isnull ((select ',' ' Name from syscolumns where id = c.id and color = a.fkey14),' '') isnull ((select ',' ' name from Syscolumns where id = c.id and color = a.fkey15), '' ') isnull ((select', '' name from syscolumns where id = c.id and color = a.fkey16), '' ''), Pkcolumns = isnull (Select Name from syscolumns where id = D.ID and color = a.rkey1), '' ') isnull ((select', '' name from syscolumns WHERE ID = D.ID and color = a.rkey2), '' ') isnull ((select', '' name from syscolumns where id = D.ID and color = a.rkey3), '' ' ') Isnull (SELECT', ''
Name from syscolumns where id = D.ID and color = a.rkey4), '' ') isnull (select', '' name from syscolumns where id = D.ID and color = a.rkey5), '' ') Isnull (Select', '' Name from syscolumns where id = D.ID and color = a.rkey6), '' ') isnull (Select', '' Name From syscolumns where id = D.ID and color = a.rkey7), '' ') isnull (select', '' name from syscolumns where id = D.ID and color = a.rkey8), ' '') ISNULL ((select ',' ' name from syscolumns where id = D.ID and color = a.rkey9),' '') isnull ((select ',' ' Name from Syscolumns where id = D.ID and color = a.rkey10), '' ') isnull ((select', '' name from syscolumns where id = D.ID and color = a.rkey11), '' '') Isnull ((select ',' ' name from syscolumns where id = D.ID and color = a.rkey12),' '') isnull (Select ',' ' Name from syscolumnsWHERE ID = D.ID and color = a.rkey13), '' ') isnull ((select', '' name from syscolumns where id = D.ID and color = a.rkey14), '' ' ') Isnull (SELECT', ''
Name from syscolumns where id = D.ID and color = a.rkey15) '' ') From sysreferences a, sysobjects b, sysobjects c, sysobjects d Where b.parent_objects where xtype =' u '') And a.constid = B.ID and A.FKEYID = C .id and a.rkeyid = d.id and c.name in (select tbname from #tbname) ') - //6 page -------------------- ---------------
- Continued exec ( 'use' @ db2 'insert # fk2 (fkname, fktbname, pktbname, fkcolumns, pkcolumns) select fkname = b.name, fktbname = c.name, pktbname = d.name, fkcolumns = isnull ( (Select Name from syscolumns where id = c.id and color = a.fkey1), '' ') isnull ((select', '' name from syscolumns where id = c.id and color = a.fKey2 ), '' ') ISNULL ((select', '' name from syscolumns where id = c.id and color = a.fkey3), '' ') isnull ((select', ') Name from syscolumns where id = c.id and color = a.fkey4), '' ') isnull (Select', '' name from syscolumns where id = c.id and color = a.fkey5) , '' ') ISNULL (Select', '' Name from syscolumns where id = c.id and color = a.fkey6), '' ') isnull ((select', '' ) Name from syscolumns where id = c.id and color = a.fkey7), '' ') isnull (select', '' name from syscolumns where id = C.ID and color = a.fk EY8), '' ') ISNULL (SELECT', '' Name from syscolumns where id = c.id and color = a.fkey9), '' ') isnull (SELECT', ' ' Name from syscolumns where id = c.id and color = a.fkey10),' '')
ISNULL (Select ',' ' Name from syscolumns where id = c.id and color = a.fkey11),' '') isnull ((select ',' ' name from syscolumns where id = c .id and color = a.fkey12), '' ') isnull (Select', '' name from syscolumns where id = c.id and color = a.fkey13), '' ') ISNULL (Select ',' ' Name from syscolumn where id = c.id and color = a.fkey14),' '') isnull ((select ',' ' name from syscolumns where id = c. ID and color = a.fkey15), '' ') isnull ((select', '' name from syscolumns where id = c.id and color = a.fkey16), '' '), pkcolumns = ISNULL (Select Name from syscolumns where id = D.ID and color = a.rkey1), '' ') isnull (Select', '' name from syscolumns where id = D.ID and color = a .rKey2), '' ') isnull (Select', '' name from syscolumns where id = D.ID and color = a.rkey3), '' ') isnull ((select') (SELECT ') '' N AME from syscolumns where id = D.ID and color = a.rkey4), '' ') isnull ((select', '' name from syscolumns where id = D.ID and color = a.rkey5), '' ') Isnull (SELECT' ',' '
Name from syscolumns where id = D.ID and color = a.rkey6), '' ') isnull (select', '' name from syscolumns where id = D.ID and color = a.rkey7), '' ') Isnull (Select', '' Name from syscolumns where id = D.ID and color = a.rkey8), '' ') isnull ((select', '' Name From syscolumns where id = D.ID and color = a.rkey9), '' ') isnull (select', '' name from syscolumns where id = D.ID and color = a.rkey10), ' '') Isnull (Select ',' ' Name from syscolumns where id = D.ID and color = a.rkey11),' '') isnull ((select ',' ' Name from Syscolumns where id = d.id and color = a.rkey12), '' ') isnull (Select', '' name from syscolumns where id = D.ID and color = a.rkey13), '' '') Isnull ((select ',' ' name from syscolumns where id = D.ID and color = a.rkey14),' '') isnull (Select ',' ' Name from syscolumnWHERE ID = D.ID and color = a.rkey15), '' ') isnull ((select', '' name from syscolumns where id = D.ID and color = a.rkey16), '' '
) From sysreference, sysobjects c, sysobjects d Where b.parent_obj in (select id from sysobjects where xtype = 'u' ') and a.constid = B.ID and A.FKEYID = C.ID and A .rkeyid = D.ID and c.Name in (select tbname from #tbname) ') --- // 6 page ----------------------- --------
- Continue to --exist in@ db1, but not exist in@ db2exec ('INSERT #difference (desc_difference) select' 'in' @ db1 ', but not in' @ db2 '', '' FOREIGN Key '', '' FOREIGN Key ON '' A.FKTBNAME '' ('' fkcolumns '') '' from # fk1 a where not exists (SELECT 1 from # fk2 b where a.fktbname = B .fktbname and a.fkcolumns = b.fkcolumns) ') - exist in @ db2, but not exist in @ db1exec (' insert #difference (desc_difference, objecttype, objectname) select '' in ' @ db2 ', but not IN ' @ db1 ' ',' FOREIGN Key '', '' Foreign Key ON '' A.FKTBNAME '' ('' fkcolumns ')' 'from # fk2 a where not exists (SELECT 1 from # fk1 b where a.fktbname = b.fktbname and a.fkcolumns = b.fkcolumns) ') - the referenced table or column is differentexec (' insert #difference (desc_difference, objecttype, objectname) select '' The referenced table or Column is different: '' a.pktbname '' (' a.pkcolumns ' ') -' @ db1 ',' ' b.pktbname ' '(' ' b.pkcolumn ') - - ' @ db2 ' ',' fo Reign Key ',' 'FOREIGN Key ON' ' A.FKTBNAME ' '(' ' a.fkcolumns ' ')' 'from # fk1 a, # fk2 b Where a.fktbname = B.fktbname and A.FKCOLUMNS = B.fkcolumns and (a.pktbname <>
b.pktbname or a.pkcolumns <> b.pkcolumns) ') - foreign key name is differentexec (' insert #difference (desc_difference, objecttype, objectname) select '' The foreign key name is different: '' a.fkname '' - ' @ db1 ', '' B.fkname '', 'FOREIGN Key' ',' 'Foreign Key ON' ' A.FKTBNAME ' '(' ' a.fkcolumns ' ')' 'from # fk1 a, # fk2 b Where a.fktbname = B.fktbname and a.fkcolumns = b.fkcolumns and a.pktbname = b.pktbname and a.pkcolumns = b.pkcolumns And a.fkname <> B.fkname ') Select * from #difference
Drop table # Difference, # tbname, # colorns1, # colorns2drop table # indexes1, # indexes2, # check1, # check2, # fk1, # fk2
// ------------ More than 600 lines, finally passed the PBSQL (Feng Yun)
/ * ---------------------------------------------
If you want to compare: How to compare the database structure on two different servers: first establish a link, then the two variables are: 'Test', 'annotherpc.test', that is, add the machine name, then execute, even No test, you can :)
-------------------------------------------------- - * /
