/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異--鄒建2003.9(引用請(qǐng)保留此信息)--*//*--調(diào)用示例execp_comparestruc" />

欧美三区_成人在线免费观看视频_欧美极品少妇xxxxⅹ免费视频_a级毛片免费播放_鲁一鲁中文字幕久久_亚洲一级特黄

比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

系統(tǒng) 2429 0
<iframe align="center" marginwidth="0" marginheight="0" src="http://www.zealware.com/csdnblog336280.html" frameborder="0" width="336" scrolling="no" height="280"></iframe>

/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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')

--得到數(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 '+@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,占用字節(jié)數(shù)=a.length,長(zhǎng)度=a.prec,小數(shù)位數(shù)=a.scale,允許空=a.isnullable,
默認(rèn)值=isnull(e.text,''''''),字段說(shuō)明=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 比較結(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.字段說(shuō)明b.字段說(shuō)明 then '字段說(shuō)明不同'
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.字段說(shuō)明b.字段說(shuō)明
order by isnull(a.表名1,b.表名2),isnull(a.序號(hào),b.序號(hào))--isnull(a.字段名,b.字段名)
go




/*--比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異

--鄒建 2003.9(引用請(qǐng)保留此信息)--*/
/*--調(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),字段說(shuō)明 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),字段說(shuō)明 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 '+@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

分享到:
評(píng)論

比較兩個(gè)數(shù)據(jù)庫(kù)的表結(jié)構(gòu)差異


更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號(hào)聯(lián)系: 360901061

您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺(jué)我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對(duì)您有幫助就好】

您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺(jué)我的文章對(duì)您有幫助,請(qǐng)用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長(zhǎng)會(huì)非常 感謝您的哦!??!

發(fā)表我的評(píng)論
最新評(píng)論 總共0條評(píng)論
主站蜘蛛池模板: 亚洲3p | 精品美女在线观看视频在线观看 | 精品一卡2卡三卡4卡免费观看 | 起碰97| 精品欧美在线观看 | 欧美在线一区二区三区欧美 | 欧美精品久久久 | 久久亚洲国产精品日日av夜夜 | 日日夜夜精品视频 | 亚洲激情一区二区 | 色婷婷99综合久久久精品 | 亚洲精品久久久久一区二区三 | 国产精品密蕾丝视频下载 | 欧美久久久无码精品亚洲日韩小说 | 欧美日韩一区在线观看 | 在线免费观看毛片 | 国产3级在线观看 | 中文字幕亚洲一区 | 欧美日韩在线一区 | 亚洲国产成人va在线观看网址 | 欧美三级免费看 | 亚洲精品久久久中文字幕 | 久久成年人视频 | 欧美日韩中文视频 | 黄桃av| 国产一区二区三区日韩欧美 | 天天干夜夜噜 | 国产香港一级毛片在线看 | 国产精品免费av | 精品欧美一区二区在线看片 | 久久伊人在 | 国产在线精品一区二区 | 国产在线播放免费 | 爱爱视频在线观看 | 色网站在线免费观看 | 欧美综合成人网 | 欧美图片激情小说 | 操人视频在线观看 | 91日本在线观看亚洲精品 | 免费看国产片在线观看 | 在线天堂中文在线资源网 |