批量表结构提取和批量建表-服务器专区

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

作者：CSDN　编辑： IT168 2005-09-09 00:00

【IT168 服务器学院】

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

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

一建立一个存储表结构的表
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

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

关注我们