黄色一级片欧美,欧美不卡一区,欧美视频在线一区二区三区

/*--比較兩個(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