比较两个数据库的表结构差异

admin

2010年9月30日 1:13 本文热度 3574

if exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_comparestructure]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)
drop   procedure   [dbo].[p_comparestructure]
GO

/*--比较两个数据库的表结构差异

可以比较两个数据库的结构差异

--邹建   2003.9(引用请保留此信息)--*/

/*--调用示例

exec   p_comparestructure   '库1','库2'
--*/
create   proc   p_comparestructure
@dbname1   varchar(250), --要比较的数据库名1
@dbname2   varchar(250) --要比较的数据库名2
as
create   table   #tb1(表名1   varchar(250),字段名   varchar(250),序号   int,标识   bit,主键   bit,类型   varchar(250),
占用字节数   int,长度   int,小数位数   int,允许空   bit,默认值   sql_variant,字段说明   sql_variant)

create   table   #tb2(表名2   varchar(250),字段名   varchar(250),序号   int,标识   bit,主键   bit,类型   varchar(250),
占用字节数   int,长度   int,小数位数   int,允许空   bit,默认值   sql_variant,字段说明   sql_variant)

--得到数据库1的结构
exec('insert   into   #tb1   SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case   when   a.status=0x80   then   1   else   0   end,
主键=case   when   exists(SELECT   1   FROM   '+@dbname1+'..sysobjects   where   xtype=''PK''   and   name   in   (
SELECT   name   FROM   '+@dbname1+'..sysindexes   WHERE   indid   in(
SELECT   indid   FROM   '+@dbname1+'..sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
)))   then   1   else   0   end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
FROM   '+@dbname1+'..syscolumns   a
left   join   '+@dbname1+'..systypes   b   on   a.xtype=b.xusertype
inner   join   '+@dbname1+'..sysobjects   d   on   a.id=d.id     and   d.xtype=''U''   and     d.name<>''dtproperties''
left   join   '+@dbname1+'..syscomments   e   on   a.cdefault=e.id
left   join   '+@dbname1+'..sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid
order   by   a.id,a.colorder')

--得到数据库2的结构
exec('insert   into   #tb2   SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case   when   a.status=0x80   then   1   else   0   end,
主键=case   when   exists(SELECT   1   FROM   '+@dbname2+'..sysobjects   where   xtype=''PK''   and   name   in   (
SELECT   name   FROM   '+@dbname2+'..sysindexes   WHERE   indid   in(
SELECT   indid   FROM   '+@dbname2+'..sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
)))   then   1   else   0   end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
FROM   '+@dbname2+'..syscolumns   a
left   join   '+@dbname2+'..systypes   b   on   a.xtype=b.xusertype
inner   join   '+@dbname2+'..sysobjects   d   on   a.id=d.id     and   d.xtype=''U''   and     d.name<>''dtproperties''
left   join   '+@dbname2+'..syscomments   e   on   a.cdefault=e.id
left   join   '+@dbname2+'..sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid
order   by   a.id,a.colorder')
--and   not   exists(select   1   from   #tb2   where   表名2=a.表名1)
select   比较结果=case   when   a.表名1   is   null   and   b.序号=1   then   '库1缺少表：'+b.表名2
when   b.表名2   is   null   and   a.序号=1   then   '库2缺少表:'+a.表名1
when   a.字段名   is   null   and   exists(select   1   from   #tb1   where   表名1=b.表名2)   then   '库1   ['+b.表名2+']   缺少字段：'+b.字段名
when   b.字段名   is   null   and   exists(select   1   from   #tb2   where   表名2=a.表名1)   then   '库2   ['+a.表名1+']   缺少字段：'+a.字段名
when   a.标识<>b.标识   then   '标识不同'
when   a.主键<>b.主键   then   '主键设置不同'
when   a.类型<>b.类型   then   '字段类型不同'
when   a.占用字节数<>b.占用字节数   then   '占用字节数'
when   a.长度<>b.长度   then   '长度不同'
when   a.小数位数<>b.小数位数   then   '小数位数不同'
when   a.允许空<>b.允许空   then   '是否允许空不同'
when   a.默认值<>b.默认值   then   '默认值不同'
when   a.字段说明<>b.字段说明   then   '字段说明不同'
else   ''   end,
*
from   #tb1   a
full   join   #tb2   b   on   a.表名1=b.表名2   and   a.字段名=b.字段名
where   a.表名1   is   null   or   a.字段名   is   null   or   b.表名2   is   null   or   b.字段名   is   null
or   a.标识<>b.标识   or   a.主键<>b.主键   or   a.类型<>b.类型
or   a.占用字节数<>b.占用字节数   or   a.长度<>b.长度   or   a.小数位数<>b.小数位数
or   a.允许空<>b.允许空   or   a.默认值<>b.默认值   or   a.字段说明<>b.字段说明
order   by   isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名)
go

该文章在 2010/9/30 1:13:15 编辑过

关键字查询

正在查询...

点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。

点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理，结合码头的业务特点，围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体，是物流码头及其他港口类企业的高效ERP管理信息系统。

点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。

点晴免费OA是一款软件和通用服务都免费，不限功能、不限时间、不限用户的免费OA协同办公管理系统。