手工来作对比既费时又费力，于是写了一段比较两数据库结构差异的SQL，比较内容有：表字段（类型、是否允许空、标识、字段顺序、默认值、默认值名称、公式、排序规则）索引（是否主键、是否聚集索引、是否唯一索引、索引名称）视图存储过程触发器函数check约束外键约束（主键表、主键列、外键名称）
有兴趣的朋友请帮忙测试一下，也许将来某个时候对你有用，还有什么内容需要增加的请提建议，谢谢
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 structures:--exist in one database but not exist in another: -- 1.table, 2.column, 3.index, 4.View, 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 referenced table, 2.column of the referenced table,-- 3.foreign key nameset nocount onset ansi_nulls off
create table #difference(id int identity(1,1),objecttype varchar(50), objectname nvarchar(400),desc_difference nvarchar(3500))create table #tbname(id int identity(1,1),tbname sysname)
--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''U'' then ''Table'' when xtype=N''V'' then ''View'' when xtype=N''P'' then ''Stored Procedure'' when xtype=N''TR'' then ''Trigger'' when xtype in(N''FN'',N''IF'',N''TF'') then ''Function'' end, name from '+@db1+'.dbo.sysobjects t where xtype in(N''U'',N''V'',N''P'',N''TR'',N''FN'',N''IF'',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''U'' then ''Table'' when xtype=N''V'' then ''View'' when xtype=N''P'' then ''Stored Procedure'' when xtype=N''TR'' then ''Trigger'' when xtype in(N''FN'',N''IF'',N''TF'') then ''Function'' end, name from '+@db2+'.dbo.sysobjects t where xtype in(N''U'',N''V'',N''P'',N''TR'',N''FN'',N''IF'',N''TF'') and not exists(select 1 from '+@db1+'.dbo.sysobjects where xtype=t.xtype and name=t.name)')
--续上--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.isnullable,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.syscolumns 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+'..''+a.name+'', 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 #columns1 where tbname=a.name) and not exists(select 1 from #columns1 where tbname=a.name and colname=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 where a.xtype=''U'' and a.id=b.id and exists(select 1 from #columns2 where tbname=a.name) and not exists(select 1 from #columns1 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'',''varchar'') 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))+'')'' when t2.name in (''nchar'',''nvarchar'') then ''(''+cast(b.length/2 as varchar(10))+'')'' when t2.name in (''decimal'',''numeric'') then ''(''+cast(b.prec as varchar(10))+'','' +cast(b.scale as varchar(10))+'')'' else '''' end+''--''+'''+@db2+''', ''Column'', a.tbname+''.''+a.colname from #columns1 a inner join #columns2 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.systypes 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 #columns1 a inner join #columns2 b on a.tbname=b.tbname and a.colname=b.colname where a.isnullable<>b.isnullable
--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 #columns2 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 #columns1 a inner join #columns2 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 #columns2 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, 'Column', a.tbname+'.'+a.colname from #columns1 a inner join #columns2 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.defaultname<>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 #columns1 a inner join #columns2 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, 'Column', a.tbname+'.'+a.colname from #columns1 a inner join #columns2 b on a.tbname=b.tbname and a.colname=b.colname where a.xusertype=b.xusertype and (a.collation is not null 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.indid and a.id=c.id and c.id=d.id and a.colid=d.colid and 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.keyno)')exec('use '+@db2+'declare @indexcol nvarchar(1000),@indexname nvarchar(128)insert #indexes2(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.indid and a.id=c.id and c.id=d.id and a.colid=d.colid and 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 #indexes2 set @indexcol=case when @indexname<>indexname then colname else @indexcol+''+''+colname end, indexcol=@indexcol, @indexname=indexnamedelete from #indexes2 where exists(select 1 from #indexes2 t where #indexes2.tbname=t.tbname and #indexes2.indexname=t.indexname and #indexes2.keyno<t.keyno)')--index exist in @db1, but not exist in @db2insert #difference(desc_difference,objecttype,objectname)select 'In '+@db1+', but not in '+@db2, 'Index', 'Index on '+tbname+'('+indexcol+')' from #indexes1 t where not exists(select 1 from #indexes2 where tbname=t.tbname and indexcol=t.indexcol)--index exist in @db2, but not exist in @db1insert #difference(desc_difference,objecttype,objectname)select 'In '+@db2+', but not in '+@db1, 'Index', 'Index on '+tbname+'('+indexcol+')' from #indexes2 t where not exists(select 1 from #indexes1 where tbname=t.tbname and indexcol=t.indexcol)--index is different on isclusteredinsert #difference(desc_difference,objecttype,objectname)select case a.isclustered when 1 then 'Clustered' else 'Not clustered' end +'--'+@db1+', ' +case b.isclustered when 1 then 'Clustered' else 'Not clustered' 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.isclustered<>b.isclustered
--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+', ' +case b.isprimarykey when 1 then 'Primary key' else 'Not primary key' 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.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 #difference(desc_difference,objecttype,objectname)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,objecttype,objectname)select ''Check ''+a.checkname+'' on table ''+a.tbname+'' is different'', ''Check'', a.checkname from #check1 a,#check2 b where a.tbname=b.tbname and a.checkname=b.checkname and a.checktext<>b.checktext')
create 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 colid=a.fkey1),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey2),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey3),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey4),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey5),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey6),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey7),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey8),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey9),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey10),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey11),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey12),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey13),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey14),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey15),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey16),''''), pkcolumns=isnull((select name from syscolumns where id=d.id and colid=a.rkey1),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey2),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey3),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey4),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey5),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey6),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey7),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey8),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey9),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey10),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey11),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey12),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey13),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey14),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey15),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey16),'''') from sysreferences a,sysobjects b,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)')
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 colid=a.fkey1),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey2),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey3),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey4),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey5),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey6),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey7),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey8),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey9),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey10),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey11),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey12),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey13),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey14),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey15),'''') +isnull((select '',''+name from syscolumns where id=c.id and colid=a.fkey16),''''), pkcolumns=isnull((select name from syscolumns where id=d.id and colid=a.rkey1),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey2),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey3),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey4),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey5),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey6),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey7),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey8),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey9),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey10),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey11),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey12),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey13),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey14),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey15),'''') +isnull((select '',''+name from syscolumns where id=d.id and colid=a.rkey16),'''') from sysreferences a,sysobjects b,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)')
--exist in @db1, but not exist in @db2exec('insert #difference(desc_difference,objecttype,objectname)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.pkcolumns+'')--' +@db2+''', ''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 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+''--'+@db2+''', ''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 #differencedrop table #difference,#tbname,#columns1,#columns2drop table #indexes1,#indexes2,#check1,#check2,#fk1,#fk2
