手工比较两数据库结构的差异既费时又费力,下面这段代码能快速地比较两数据库结构的差异,比较内容:
表
字段(类型、是否允许空、标识、字段顺序、默认值、默认值名称、公式、排序规则)
索引(是否主键、是否聚集索引、是否唯一索引、索引名称)
视图
存储过程
触发器
函数
check约束
外键约束(主键表、主键列、级联更新、级联删除、外键名称)
可连接任何数据库运行,只需制定变量@db1、@db2即可
declare @db1 varchar(50),@db2 varchar(50),@collation varchar(100)
select @db1='test',@db2='test1'--name of the database to compare
set @collation='collate Latin1_General_BIN'--collation of the two databases to compare
--Author: pbsql
--Date: 2005-12-22
--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.cascade update and cascade delete, 4.foreign key name
set nocount on
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 @db2
exec('
insert #tbname(tbname)
select name
from '+@db1+'.dbo.sysobjects t
where xtype=''U''
and exists(select 1 from '+@db2+'.dbo.sysobjects
where xtype '+@collation+'=t.xtype '+@collation+'
and name '+@collation+'=t.name '+@collation+')
order by name
')
--objects exist in one database, but not exist in another
exec('
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 '+@collation+'=t.xtype '+@collation+'
and name '+@collation+'=t.name '+@collation+')
union all
select ''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 '+@collation+'=t.xtype '+@collation+'
and name '+@collation+'=t.name '+@collation+')
')
--all columns and column property
create 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('
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 '+@collation+'=b.name '+@collation+'),
c.[text],b.colorder,isnull(b.prec,0),isnull(b.scale,0),
d.[text],b.isnullable,b.[collation],
case b.status when 0x80 then 1 else 0 end,
ident_seed('''+@db1+'.dbo.'''+'+a.name),
ident_incr('''+@db1+'.dbo.'''+'+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 '+@collation+'=e.name '+@collation+'
and b.name '+@collation+'=f.name '+@collation+')
order by a.name,b.colorder
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 '+@collation+'=b.name '+@collation+'),
c.[text],b.colorder,isnull(b.prec,0),isnull(b.scale,0),
d.[text],b.isnullable,b.[collation],
case b.status when 0x80 then 1 else 0 end,
ident_seed('''+@db2+'.dbo.'''+'+a.name),
ident_incr('''+@db2+'.dbo.'''+'+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 '+@collation+'=e.name '+@collation+'
and b.name '+@collation+'=f.name '+@collation+')
order by a.name,b.colorder
')
--column exist in @db1, but not exist in @db2
exec('
insert #difference(desc_difference,objecttype,objectname)
select desc_difference,objecttype,objectname
from
(
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 #tbname
where tbname '+@collation+'=a.name '+@collation+')
and not exists(select 1 from #columns1
where tbname '+@collation+'=a.name '+@collation+'
and colname '+@collation+'=b.name '+@collation+')
order by a.name,b.colorder
) t
')
--column exist in @db2, but not exist in @db1
exec('
insert #difference(desc_difference,objecttype,objectname)
select desc_difference,objecttype,objectname
from
(
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 #tbname
where tbname '+@collation+'=a.name '+@collation+')
and not exists(select 1 from #columns1
where tbname '+@collation+'=a.name '+@collation+'
and colname '+@collation+'=b.name '+@collation+')
order by a.name,b.colorder
) t
')
--column data type is different
exec('
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 '+@collation+'=b.tbname '+@collation+'
and a.colname '+@collation+'=b.colname '+@collation+'
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 different
insert #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 different
insert #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 different
insert #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 different
insert #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 different
insert #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 different
insert #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 different
insert #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 index
create 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('
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=case when exists(select 1 from '+@db1+'.dbo.sysobjects
where (xtype=''UQ'' or xtype=''PK'')
and parent_obj=a.id
and name '+@collation+'=b.name '+@collation+')
then 1 else 0 end,
isclustered=case when b.indid=1 then 1 else 0 end,
isprimarykey=case when exists(select 1 from '+@db1+'.dbo.sysobjects
where xtype=''PK''
and parent_obj=a.id
and name '+@collation+'=b.name '+@collation+')
then 1 else 0 end
from '+@db1+'.dbo.sysindexkeys a,'+@db1+'.dbo.sysindexes b,'
+@db1+'.dbo.sysobjects c,'+@db1+'.dbo.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 b.name not like ''_WA_Sys_%''
and exists(select 1 from #tbname
where tbname '+@collation+'=c.name '+@collation+')
order by tbname,indexname,keyno
select @indexcol='''',@indexname=''''
update #indexes1
set @indexcol=case when @indexname<>indexname
then colname else @indexcol+''+''+colname end,
indexcol=@indexcol,
@indexname=indexname
delete 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('
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=case when exists(select 1 from '+@db2+'.dbo.sysobjects
where (xtype=''UQ'' or xtype=''PK'')
and parent_obj=a.id
and name '+@collation+'=b.name '+@collation+')
then 1 else 0 end,
isclustered=case when b.indid=1 then 1 else 0 end,
isprimarykey=case when exists(select 1 from '+@db2+'.dbo.sysobjects
where xtype=''PK''
and parent_obj=a.id
and name '+@collation+'=b.name '+@collation+')
then 1 else 0 end
from '+@db2+'.dbo.sysindexkeys a,'+@db2+'.dbo.sysindexes b,'
+@db2+'.dbo.sysobjects c,'+@db2+'.dbo.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 b.name not like ''_WA_Sys_%''
and exists(select 1 from #tbname
where tbname '+@collation+'=c.name '+@collation+')
order by tbname,indexname,keyno
select @indexcol='''',@indexname=''''
update #indexes2
set @indexcol=case when @indexname<>indexname
then colname else @indexcol+''+''+colname end,
indexcol=@indexcol,
@indexname=indexname
delete 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 @db2
insert #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 @db1
insert #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 isclustered
insert #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 isunique
insert #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 isprimarykey
insert #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 different
insert #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 @db2
exec('
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 '+@collation+'=t.name '+@collation+')
')
--Check exist in @db2, but not exist in @db1
exec('
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 '+@collation+'=t.name '+@collation+')
')
--Compare check constraints
create 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 '+@collation+'=a.name '+@collation+')
')
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 '+@collation+'=a.name '+@collation+')
')
--Check constraint text is different
exec('
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
')
--Compare foreign key constraint
create table #fk1(fkname sysname,fktbname sysname,pktbname sysname,
fkcolumns nvarchar(1800),pkcolumns nvarchar(1800),
cascade_update bit,cascade_delete bit)
create table #fk2(fkname sysname,fktbname sysname,pktbname sysname,
fkcolumns nvarchar(1800),pkcolumns nvarchar(1800),
cascade_update bit,cascade_delete bit)
exec('
insert #fk1(fkname,fktbname,pktbname,fkcolumns,pkcolumns,cascade_update,cascade_delete)
select fkname=b.name,
fktbname=c.name,
pktbname=d.name,
fkcolumns=isnull((select name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey1),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey2),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey3),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey4),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey5),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey6),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey7),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey8),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey9),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey10),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey11),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey12),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey13),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey14),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey15),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=c.id and colid=a.fkey16),''''),
pkcolumns=isnull((select name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey1),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey2),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey3),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey4),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey5),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey6),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey7),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey8),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey9),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey10),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey11),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey12),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey13),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey14),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey15),'''')
+isnull((select '',''+name from '+@db1+'.dbo.syscolumns
where id=d.id and colid=a.rkey16),''''),
cascade_update=(b.status/8192)%2,
cascade_delete=(b.status/4096)%2
from '+@db1+'.dbo.sysreferences a,'+@db1+'.dbo.sysobjects b,'
+@db1+'.dbo.sysobjects c,'+@db1+'.dbo.sysobjects d
where b.parent_obj in(select id from '+@db1+'.dbo.sysobjects
where xtype=''U'')
and a.constid=b.id
and a.fkeyid=c.id
and a.rkeyid=d.id
and c.name '+@collation+' in(select tbname from #tbname)
')
exec('
insert #fk2(fkname,fktbname,pktbname,fkcolumns,pkcolumns,cascade_update,cascade_delete)
select fkname=b.name,
fktbname=c.name,
pktbname=d.name,
fkcolumns=isnull((select name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey1),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey2),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey3),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey4),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey5),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey6),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey7),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey8),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey9),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey10),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey11),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey12),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey13),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey14),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey15),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=c.id and colid=a.fkey16),''''),
pkcolumns=isnull((select name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey1),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey2),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey3),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey4),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey5),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey6),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey7),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey8),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey9),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey10),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey11),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey12),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey13),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey14),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey15),'''')
+isnull((select '',''+name from '+@db2+'.dbo.syscolumns
where id=d.id and colid=a.rkey16),''''),
cascade_update=(b.status/8192)%2,
cascade_delete=(b.status/4096)%2
from '+@db2+'.dbo.sysreferences a,'+@db2+'.dbo.sysobjects b,'
+@db2+'.dbo.sysobjects c,'+@db2+'.dbo.sysobjects d
where b.parent_obj in (select id from '+@db2+'.dbo.sysobjects
where xtype=''U'')
and a.constid=b.id
and a.fkeyid=c.id
and a.rkeyid=d.id
and c.name '+@collation+' in(select tbname from #tbname)
')
--exist in @db1, but not exist in @db2
exec('
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 @db1
exec('
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 different
exec('
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)
')
--the cascade update rule is different
exec('
insert #difference(desc_difference,objecttype,objectname)
select ''The cascade update rule is different: ''
+case a.cascade_update when 1 then ''Cascade Update''
else ''No Action'' end
+''--' +@db1+', ''
+case b.cascade_update when 1 then ''Cascade Update''
else ''No Action'' end
+''--' +@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.cascade_update<>b.cascade_update
')
--the cascade delete rule is different
exec('
insert #difference(desc_difference,objecttype,objectname)
select ''The cascade delete rule is different: ''
+case a.cascade_delete when 1 then ''Cascade Delete''
else ''No Action'' end
+''--' +@db1+', ''
+case b.cascade_delete when 1 then ''Cascade Delete''
else ''No Action'' end
+''--' +@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.cascade_delete<>b.cascade_delete
')
--foreign key name is different
exec('
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 #difference
drop table #difference,#tbname,#columns1,#columns2
drop table #indexes1,#indexes2,#check1,#check2,#fk1,#fk2