应用地址 http://blog.csdn.net/pbsql/article/details/552165

 

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