如何快速地比较两数据库结构的差异

Question

手工来作对比既费时又费力，于是写了一段比较两数据库结构差异的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 name
set nocount on
set ansi_nulls offcreate 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=t.xtype and name=t.name)
  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=t.xtype and name=t.name)
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=t.xtype and name=t.name)
')

Answer 1

--续上
--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('
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.colorder
use '+@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 @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 #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 @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 #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 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=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 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

Answer 2

--续上
--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('
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,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('
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,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

Answer 3

--续上
--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=t.name)
')
--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=t.name)
')--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=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 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
')

Answer 4

--续上
--Compare foreign key constraint
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)
')

Answer 5

--续上
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)
')

Answer 6

--续上
--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)
')
--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 #differencedrop table #difference,#tbname,#columns1,#columns2
drop table #indexes1,#indexes2,#check1,#check2,#fk1,#fk2

Answer 7

高手阿 SQL Server 啊 努力 奋斗-----------------------------------------------------

Answer 8

放上blog, 贴个地址过来吧, 这样看起来舒服很多.

Answer 9

已放到blog：
http://blog.csdn.net/pbsql/archive/2005/12/14/552165.aspx

Answer 10

如何比较两台不同服务器上的数据库结构：
先建立链接，再将两个变量值为：'test','anotherpc.test'，即在数据库名前加上机器名，再执行，偶没测试过，按理可以:)

Answer 11

不同服務器好像是不行，不能用USE開其它服務器的數據庫

Answer 12

將USE語句去掉就可以啦，不過先要連接另一個服務器

Answer 13

因为要用COLUMNPROPERTY、INDEXPROPERTY等函数取表的列属性、索引属性，这些函数跨库执行不行（返回null），所以只能use数据库后再取，而use是不能跨越服务器，所以跨越服务器比较就不行了

Answer 14

zlj113：数据库结构拿到本机来比较就是了其他各位有什么建议？

Answer 15

好消息，现在可以比较两台不同服务器上的数据库结构了！！！
方法：
先建立另一台机器的SQL链接，再将两个变量值为：'test','anotherpc.test'（或者[192.168.0.1].test），即在数据库名前加上机器名或者IP，再执行比较的代码即可SQL地址：
http://blog.csdn.net/pbsql/archive/2005/12/14/552165.aspx修改了原SQL中的一个BUG（两数据库排序规则不同时会出错），在比较外键约束时增加了是否是级联更新、级联删除的比较，还有就是可以比较两台不同服务器上的数据库有兴趣的请帮忙测试一下，谢谢！！！

Answer 16

这样的事情我一般都用powerdesigner来解决
用制取结构可以把数据库中的结构取出来
然后就可以比较了，PD比较的结果还是不错的
你还可以直接产生更新脚本，这样就可以把生产库调整成和开发库相同了
PD可以尽量不破坏数据，必要时自动用临时表解决问题
而且PD支持很多数据库，以上工作我们甚至可以在不同的数据库中完成
不过还是很佩服楼主的技术

Answer 17

set @collation='collate Latin1_General_BIN'
的作用是什么？

Answer 18

--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 #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 @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 #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
')如果不去掉以上两行，当两个同名表A和B,A的所有列名和B的都不同时，它就检测不出来了。

Answer 19

更正:风去--->风云 (五笔也有坏处.fcu默认为去,选2为云)

Answer 20

多谢liuqd(liuqd)，那个是个错误的条件，SQL已更改：
http://blog.csdn.net/pbsql/archive/2005/12/14/552165.aspx

Answer 21

如果两个数据库的排序规则不同，那么相应的系统表上字段（如sysobjects.xtype）的排序规则也会不同，此时若不加排序规则将两表进行连接查询（where a.name=b.name）会出错，使用同样的排序规则就不会出错了（where a.name collate Latin1_General_BIN=b.name collate Latin1_General_BIN）。set @collation='collate Latin1_General_BIN'
的作用就在于此

Answer 22

为何@collation只在exec()内用，而其它地方都不用。

Answer 23

为何@collation只在exec()内用，而其它地方都不用。因为只有exec()内部才涉及到跨库操作，才会有排序规则不同的问题，而同一库中系统表的排序规则都是一样的（都是那个数据库的排序规则），所以不需要加排序规则。而临时表使用的排序规则默认应该是当前连接的数据库的排序规则，所有临时表都是一样所以也不需要。

Answer 24

to ： pbsql(风云)可以编写程序来跨服务器啊

Answer 25

请问一下楼主，能不能把INDEX分成PK，UQ，IX，FK？我找了好久没有找到，如果行的请恢复，谢谢

Answer 26

zhonghuashen(Euler)：
实际上现在的SQL中都包含了PK，UQ，IX，FK的所有属性的比较的，不同点也是分开了的之所以把前三者（PK，UQ，IX）放到了一段程序中处理，主要是它们有共性（都是索引），这样写程序节约代码行，运行速度快点而FK是外键，它的属性明显与其它三个不同，所涉及的系统表也不同，所以单独作了比较

Answer 27

lj mm还在蹂躏jf的帐号哈，向风云学习一下