比较两数据库的差异

转载 2006年06月15日 09:17:00

使用说明:

        我们在开发数据库应用程序时,通常会直接通过企业管理器往表里加字段,或新建表,或修改类型,
对于以有的客户,就需要更新数据库结构,而此时如手工查找时相当费时而且易出错.
居于上述需求特写两个存储过程,
1个是比较两者不同表结构(此处不比较存储过程和触发器)
2.是自动修改客户的表结构使之和我们新数据库表结构一致.(自动修改不是很完美,
比如主键,默认值,索引不同,都做不到,但这不会影响我们,因为大都改动无非四种情况:
缺少表,2缺少字段,3字段类型不同 4字段长度不同)

 

比较两数据库的结构(完全版):

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

  --*/
  /*
调用示例

  exec p_comparestructure 'kst2','yuxi'
 */

   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

  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,默认值 varchar(500),字段说明 varchar(500))

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

  --得到数据库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,
  a.* into #
   from #tb1 a
  left join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名
  where  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 a.表名1,a.序号

  select * from # where len(比较结果)>0
  go
 

比较并修改两数据库的结构:


/*
 功能:核对两数据库表结构,并修改副数据库使之和主数据库一致
 可分为三种情况:  1.缺表
   2.缺字段
   3.字段类型不一致
   4.长度不同
 作者:WGS
 创建时间:2006-01-13
 修改时间:2006-01-13
 调用示例: 
 exec comparestb_update 'tmpcompdb','lren'
 执行完后查看是否全部都改好了:
 exec p_comparestructure 'kst2','yuxi'
*/
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[comparestb_update]') and     OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[comparestb_update]
GO

  create proc [dbo].[comparestb_update]
  @dbname1 varchar(250), --要比较的数据库(即你开发一直用的最新数据库)
  @dbname2 varchar(250) --需要更新的数据库(即老数据库)
  as
  set nocount on
  create table #tb1(tbname varchar(250),colid int,colname varchar(250),coltype varchar(250),length varchar(4),prec int,scale int)
  create table #tb2(tbname varchar(250),colid int,colname varchar(250),coltype varchar(250),length varchar(4))

  --得到数据库1的结构
  exec('insert into #tb1 SELECT
  c.name,a.colid,a.name,b.name,cast(a.length as varchar),a.prec,a.scale
  FROM '+@dbname1+'.dbo.syscolumns a
  left join '+@dbname1+'.dbo.systypes b on a.xtype=b.xusertype
  inner join '+@dbname1+'.dbo.sysobjects c on a.id=c.id and c.xtype=''u'' and c.name not like ''__jiesuan%''
  order by a.id,a.colorder')

  --得到数据库2的结构
  exec('insert into #tb2 SELECT
  c.name,a.colid,a.name,b.name,cast(a.length as varchar)
  FROM '+@dbname2+'.dbo.syscolumns a
  left join '+@dbname2+'.dbo.systypes b on a.xtype=b.xusertype
  inner join '+@dbname2+'.dbo.sysobjects c on a.id=c.id and c.xtype=''u'' and c.name not like ''__jiesuan%''
  order by a.id,a.colorder')

  --and not exists(select 1 from #tb2 where 表名2=a.表名1)
  select flag=case when b.tbname is null and a.colid=1  then 1
  when b.colname is null and exists(select 1 from #tb2 where tbname=a.tbname) then 2
  when a.coltype<>b.coltype then 3
  when a.length<>b.length then 4 end,a.* into #
  from #tb1 a left join #tb2 b on a.tbname=b.tbname and a.colname=b.colname
  where b.tbname is null  or a.coltype<>b.coltype
  order by a.tbname,a.colid

declare @flag int,@tbname varchar(200),@colname varchar(200),@coltype varchar(200),
@length varchar(4),@sql varchar(4000),@prec varchar(4),@scale varchar(4)
declare cur cursor for
select flag,tbname,colname,coltype,length,cast(prec as varchar) prec,cast(scale as varchar) scale from # where flag is not null
open cur
fetch next from cur into @flag,@tbname,@colname,@coltype,@length,@prec,@scale
while @@fetch_status=0
begin
 if @flag=1 --缺表
 set @sql='select * into ['+@dbname2+'].[dbo].['+@tbname+']  from ['+@dbname1+'].[dbo].['+@tbname+'] where 1>2'
 else
 begin
 if @flag=2 --缺字段
  set @sql='alter table ['+@dbname2+'].[dbo].['+@tbname+'] add ['+@colname+'] '+@coltype+
  case  when @coltype in('char','varchar','nchar','nvarchar','varbinary','binary') then '('+@length+')'
  when @coltype in('decimal','numeric') then '('+@prec+','+@scale+')'
  else '' end
 else --字段类型不同或长度不同(flag=3 or flag=4)
  set @sql='alter table ['+@dbname2+'].[dbo].['+@tbname+'] alter column '+@colname+' '+@coltype+
  case  when @coltype in('char','varchar','nchar','nvarchar','varbinary','binary') then '('+@length+')'
  when @coltype in('decimal','numeric') then '('+@prec+','+@scale+')'
  else '' end
 end
 
 exec(@sql)
 fetch next from cur into @flag,@tbname,@colname,@coltype,@length,@prec,@scale
end
close cur
deallocate cur
  go

 

 

【SHELL】如何对比两个数据库差异?

今天一个同事碰到一个问题:要比较两个数据库文件的差异,数据量太大,一个一个看是不现实的,有什么办法更快一点呢?中午睡觉的时候,作了一些尝试,终于想到一个办法。具体的办法如下:一、先安装sqlite3,...
  • u013819292
  • u013819292
  • 2016年06月07日 15:35
  • 1382

SQL SERVER 比较两个数据库中表和字段的差异

在开发过程中线上的数据库表字段和本地数据库表字段是存在的,也许我们在本地数据库中所增加的表字段都会有记录到SQL文件中,但当增加的表及字段名称较多时总会出现漏网之鱼,发布真是版本的时候回出现很多很多的...
  • XPY567
  • XPY567
  • 2016年11月29日 20:01
  • 3269

不用比较运算符比较两个数的大小

纪念一下 不准在函数体中使用任何比较运算,比如if,for, while等,不准调用任何库函数。 int max(int a,int b) { int sa,sb,f; sa...
  • bakw
  • bakw
  • 2013年08月21日 10:16
  • 790

利用PowerDesigner比较2个数据库结构

.PowerDesigner中可以对2个数据模型进行比较,所以想到用这个功能来实现对比数据库的目的.到底怎样利用PowerDesigner来比较新旧数据库,查看数据库中哪些对象变化了呢,下面慢慢道来....
  • qwlovedzm
  • qwlovedzm
  • 2011年08月30日 20:54
  • 2729

比较两张图片的差异

比较图片
  • lj2tj
  • lj2tj
  • 2016年12月16日 12:55
  • 1021

SQL Server 比较两个数据库的视图和存储过程结构差异

IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo]....
  • tuzhen007
  • tuzhen007
  • 2014年02月26日 11:28
  • 1033

比较两数据库的结构(收藏)

SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GOcreate    PROCEDURE r_pro_xx@oldserver varchar(50),@o...
  • zhang_bamboo
  • zhang_bamboo
  • 2010年08月03日 11:10
  • 383

C# 文件比较差异

参考:https://www.cnblogs.com/vaevvaev/p/7115721.html 这里我就比较2个文件 使用了fc命令。 2个文件路径如下  path1,path2 stati...
  • hanjun0612
  • hanjun0612
  • 2017年12月13日 14:04
  • 114

oracle两个数据库之间的内容对比

由于公司项目生产环境的数据库(项目第五版)需要升级(项目第六版),其中有一些数据表,序列,存储过程需要和5期进行同步,如果直接导出5期的数据库脚本和6期的数据库脚本进行对比的话,耗时耗力,准确率也不够...
  • u012492359
  • u012492359
  • 2017年09月26日 10:53
  • 165

如何比较两个Oracle数据库之间对象的不同

 关于这点,PL/SQL 已经帮我们做了菜单: Tools -> Compare User Objects进入后选定需要比较的对象,再指定比较目标数据库,即可完成比较。...
  • likelynn
  • likelynn
  • 2008年02月24日 15:51
  • 2433
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:比较两数据库的差异
举报原因:
原因补充:

(最多只允许输入30个字)