/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異
--*/
/*--調(diào)用示例
exec p_comparestructure 'xzkh_model','xzkh_new'
--*/
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), --要比較的數(shù)據(jù)庫(kù)名1
@dbname2 varchar(250) --要比較的數(shù)據(jù)庫(kù)名2
as
create table #tb1(表名1 varchar(250),字段名 varchar(250),序號(hào) int,標(biāo)識(shí) bit,主鍵 bit,類型 varchar(250),
占用字節(jié)數(shù) int,長(zhǎng)度 int,小數(shù)位數(shù) int,允許空 bit,默認(rèn)值 varchar(500),字段說明 varchar(500))
create table #tb2(表名2 varchar(250),字段名 varchar(250),序號(hào) int,標(biāo)識(shí) bit,主鍵 bit,類型 varchar(250),
占用字節(jié)數(shù) int,長(zhǎng)度 int,小數(shù)位數(shù) int,允許空 bit,默認(rèn)值 varchar(500),字段說明 varchar(500))
--得到數(shù)據(jù)庫(kù)1的結(jié)構(gòu)
exec('insert into #tb1 select
表名=d.name,字段名=a.name,序號(hào)=a.colid,
標(biāo)識(shí)=case when a.status=0x80 then 1 else 0 end,
主鍵=case when exists(select 1 from '[email protected]+'..sysobjects where xtype=''pk'' and name in (
select name from '[email protected]+'..sysindexes where indid in(
select indid from '[email protected]+'..sysindexkeys where id = a.id and colid=a.colid
))) then 1 else 0 end,
類型=b.name, 占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale, 允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說明=isnull(g.[value],'''''')
from '[email protected]+'..syscolumns a
left join '[email protected]+'..systypes b on a.xtype=b.xusertype
inner join '[email protected]+'..sysobjects d on a.id=d.id and d.xtype=''u'' and d.name<>''dtproperties''
left join '[email protected]+'..syscomments e on a.cdefault=e.id
left join '[email protected]+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--得到數(shù)據(jù)庫(kù)2的結(jié)構(gòu)
exec('insert into #tb2 select
表名=d.name,字段名=a.name,序號(hào)=a.colid,
標(biāo)識(shí)=case when a.status=0x80 then 1 else 0 end,
主鍵=case when exists(select 1 from '[email protected]+'..sysobjects where xtype=''pk'' and name in (
select name from '[email protected]+'..sysindexes where indid in(
select indid from '[email protected]+'..sysindexkeys where id = a.id and colid=a.colid
))) then 1 else 0 end,
類型=b.name, 占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale, 允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說明=isnull(g.[value],'''''')
from '[email protected]+'..syscolumns a
left join '[email protected]+'..systypes b on a.xtype=b.xusertype
inner join '[email protected]+'..sysobjects d on a.id=d.id and d.xtype=''u'' and d.name<>''dtproperties''
left join '[email protected]+'..syscomments e on a.cdefault=e.id
left join '[email protected]+'..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 比較結(jié)果=case when a.表名1 is null and b.序號(hào)=1 then '庫(kù)1缺少表：'+b.表名2
when b.表名2 is null and a.序號(hào)=1 then '庫(kù)2缺少表:'+a.表名1
when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then '庫(kù)1 ['+b.表名2+'] 缺少字段：'+b.字段名
when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then '庫(kù)2 ['+a.表名1+'] 缺少字段：'+a.字段名
when a.標(biāo)識(shí)<>b.標(biāo)識(shí) then '標(biāo)識(shí)不同'
when a.主鍵<>b.主鍵 then '主鍵設(shè)置不同'
when a.類型<>b.類型 then '字段類型不同'
when a.占用字節(jié)數(shù)<>b.占用字節(jié)數(shù) then '占用字節(jié)數(shù)'
when a.長(zhǎng)度<>b.長(zhǎng)度 then '長(zhǎng)度不同'
when a.小數(shù)位數(shù)<>b.小數(shù)位數(shù) then '小數(shù)位數(shù)不同'
when a.允許空<>b.允許空 then '是否允許空不同'
when a.默認(rèn)值<>b.默認(rèn)值 then '默認(rèn)值不同'
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.標(biāo)識(shí)<>b.標(biāo)識(shí) or a.主鍵<>b.主鍵 or a.類型<>b.類型
or a.占用字節(jié)數(shù)<>b.占用字節(jié)數(shù) or a.長(zhǎng)度<>b.長(zhǎng)度 or a.小數(shù)位數(shù)<>b.小數(shù)位數(shù)
or a.允許空<>b.允許空 or a.默認(rèn)值<>b.默認(rèn)值 or a.字段說明<>b.字段說明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go
