比较两数据库的差异

使用说明:

        我们在开发数据库应用程序时,通常会直接通过企业管理器往表里加字段,或新建表,或修改类型,
对于以有的客户,就需要更新数据库结构,而此时如手工查找时相当费时而且易出错.
居于上述需求特写两个存储过程,
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