△在进行系统设计和测试时,经常需要建立多库...... 在进行系统设计和测试时,经常需要建立多库,并且各库内容相同。一般思路是导入或者是复制表的脚本来一个一个的建表,显然这样操作费时繁琐,而且不能保证索引等信息全部都一样。本文介绍的是如何生成一个库的表结构,并通过该表结构反向生成表。 一 建立一个存储表结构的表 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 上述方法不适合于复合索引。 |
|小黑屋|最新主题|手机版|微赢网络技术论坛 ( 苏ICP备08020429号 )
GMT+8, 2024-9-29 17:24 , Processed in 0.268704 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.