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

手工来作对比既费时又费力，于是写了一段比较两数据库结构差异的SQL，比较内容有：
表
字段（类型、是否允许空、标识、字段顺序、默认值、默认值名称、公式、排序规则）
索引（是否主键、是否聚集索引、是否唯一索引、索引名称）
视图
存储过程
触发器
函数
check约束
外键约束（主键表、主键列、外键名称）

可连接任何数据库运行，只需制定变量@db1、@db2即可

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 off

create table #difference(id int identity(1,1),objecttype varchar(50),
  objectname nvarchar(400),desc_difference nvarchar(3500))
create table #tbname(id int identity(1,1),tbname sysname)

--all user table exist in @db1, and also exist in @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)
')

--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 #differenc