批量表结构提取和批量建表-数据库

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

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

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