分享
 
 
 

[原创]批量表结构提取和批量建表

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

批量表结构提取和批量建表

在进行系统设计和测试时,经常需要建立多库,并且各库内容相同。一般思路是导入或者是复制表的脚本来一个一个的建表,显然这样操作费时繁琐,而且不能保证索引等信息全部都一样。本文介绍的是如何生成一个库的表结构,并通过该表结构反向生成表。

一 建立一个存储表结构的表

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablestruc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tablestruc]

GO

CREATE TABLE [dbo].[tablestruc] (

[表名] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[表说明] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

[字段名] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[字段说明] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,

[标识] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[主键] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[类型] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,

[占用字节数] [smallint] NOT NULL ,

[允许空] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[默认值] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NOT NULL ,

[长度] [int] NULL ,

[小数位数] [int] NOT NULL ,

[字段序号] [smallint] NOT NULL ,

[索引] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

二 提取表结构

SELECT

表名=case when a.colorder=1 then d.name else '' end,

表说明=case when a.colorder=1 then convert(nvarchar(100) ,isnull(f.value,'')) else '' end,

字段名=a.name,

字段说明=convert(nvarchar(100),isnull(g.[value],'')),

标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,

主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (

SELECT name FROM sysindexes WHERE indid in(

SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid

))) then '√' else '' end,

类型=b.name,

占用字节数=a.length,

允许空=case when a.isnullable=1 then '√'else '' end,

默认值=isnull(e.text,''),

长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),

小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),

字段序号=a.colorder,

索引=(SELECT top 1 name FROM sysindexes m , sysindexkeys n WHERE m.id=n.id and m.indid=n.indid and n.colid=a.colid and m.id=a.id)

FROM syscolumns a

left join systypes b on a.xusertype=b.xusertype

inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join syscomments e on a.cdefault=e.id

left join sysproperties g on a.id=g.id and a.colid=g.smallid

left join sysproperties f on d.id=f.id and f.smallid=0

--如果只查询指定表,加上此条件;查询所有表,去除此条件

order by d.name,a.id,a.colorder

三 建表

f exists(select name from sysobjects

where name='c_createTable' and type='p')

drop procedure dbo.c_createTable

go

create procedure dbo.c_createTable

as

SET NOCOUNT ON

declare cursorName cursor for

select 表名,表说明,字段名,字段说明,标识,主键,类型,占用字节数,允许空,默认值,索引 from dbo.[tablestruc]

open cursorName

while 1=1

begin

declare @TableName nvarchar(50)

declare @TableName1 nvarchar(50)

declare @TableDescription nvarchar(50)

declare @columnName nvarchar(50)

declare @identityFlag nvarchar(50)

declare @keyFlag nvarchar(50)

declare @columnType nvarchar(50)

declare @TypeLength smallint

declare @Nullflag nvarchar(50)

declare @columnDefault nvarchar(50)

declare @columnDescription nvarchar(50)

declare @IndexName nvarchar(50) --索引

fetch next from cursorname into @TableName1, @TableDescription, @columnName, @columnDescription,@identityFlag, @keyFlag, @columnType, @TypeLength, @Nullflag, @columnDefault,@IndexName

if @@fetch_status<>0

break

if @tablename1 is not null and @tablename1<>' '

select @tablename=@tablename1

if @columnType='nvarchar' or @columnType='nchar'

select @TypeLength=@TypeLength/2

if @TypeLength>4000

select @TypeLength=4000

if @columnType='varchar' or @columnType='nvarchar' or @columnType='char' or @columnType='nchar'

select @columnType=@columnType+'('+ltrim(str(@TypeLength))+')'

if @nullflag='√'

select @nullflag=''

else

select @nullflag='not null'

if @columnDefault<>''

begin

if @columnType='int' or @columnType='tinyint' or @columnType='smallint'

select @columnDefault='default '+@columnDefault

else

select @columnDefault='default '+char(39)+@columnDefault+char(39)

end

if @identityFlag='√'

select @identityFlag='identity(1,1)'

else

select @identityFlag=''

if @keyFLag='√'

select @keyFlag='PRIMARY KEY '

else

select @keyFlag=''

if @tablename1 is not null and @tablename1<>' '

begin

print @tablename

print 'create table '+ @TableName+'('+@columnName+' '+@columnType+' '+@nullflag+' '+@identityFlag+

' '+ @keyFlag+' '+@columnDefault +')'

if exists(select name from sysobjects where name=@tablename and type='u')

exec ('drop table '+ @tablename)

exec ('create table '+ @TableName+'('+@columnName+' '+@columnType+' '+@nullflag+' '+@identityFlag+

' '+ @keyFlag+' '+@columnDefault +')')

if @tableDescription is not null and @tableDescription<>' '

EXEC sp_addextendedproperty 'MS_Description', @tableDescription, 'user', dbo, 'table', @TableName

if @columnDescription is not null and @columnDescription<>' '

EXEC sp_addextendedproperty 'MS_Description', @columnDescription, 'user', dbo, 'table', @TableName, 'column', @columnName

end

else

begin

exec (' alter table '+@TableName +' add '+@columnName+' '+@columnType+' '+@identityFlag+' '+@columnDefault)

if @columnDescription is not null and @columnDescription<>' '

EXEC sp_addextendedproperty 'MS_Description', @columnDescription, 'user', dbo, 'table', @TableName, 'column', @columnName

if @nullflag='not null'

exec (' alter table '+@TableName +' alter column '+@columnName+' '+@columnType+ ' not null')

if @keyFlag='PRIMARY KEY '

exec (' alter table '+@TableName +' add constraint '+'pk_'+@TableName+'_'+@columnName+' '+' primary key('+@columnName+')')

else

if isnull(@indexName,'')<>''

begin

exec(' create index ' + @indexName+ ' on ' + @tablename+ ' ( '+@columnName+' )')

select @indexName=''

end

end

end

close cursorname

deallocate cursorname

go

上述方法不适合于复合索引。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有