/*--比較兩個(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)論
主站蜘蛛池模板: 色综合天天综合网国产成人网 | 九九综合九九 | 日韩一级一欧美一级国产 | 欧美视频在线一区二区三区 | 欧美三级视频 | 免费成人在线网站 | 奇米影视第四色7777 | 五月久久亚洲七七综合中文网 | 日产一卡二卡乱码免费 | 国产欧美久久一区二区三区 | 色婷婷一区二区三区四区 | jizzyouxxxx| 免费毛片在线视频 | 又大又粗进出白浆直流动态图 | 国产久| av在线视 | 精品在线视频一区 | 色婷婷综合久久久久中文 | 久久久久久久久淑女av国产精品 | 免费二区| 久草网站 | 欧美交性又色又爽又黄 | 国产免费一级淫片 | 一道本不卡一区 | 9999人体做爰大胆视频 | 国产不卡在线 | 国产精品一区欧美激情 | 欧美三级在线播放 | 999国产视频| 波多野结衣一区二区三区88 | 伊人久久大杳蕉综合大象 | 久久久久亚洲精品中文字幕 | 99视频有精品视频高清 | 国产一级特黄aa大片爽爽 | 国产综合av | 国产精选91热在线观看 | 日韩在线观看中文 | 草久网| 播播成人网 | 99这里只有精品视频 | 成人性生交A片免费看麻豆 色倩网站 |