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