[原創]批量表結構提取和批量建表

批量表結構提取和批量建表

在進行系統設計和測試時，經常需要建立多庫，并且各庫內容相同。一般思路是導入或者是復制表的腳本來一個一個的建表，顯然這樣操作費時繁瑣，而且不能保證索引等信息全部都一樣。本文介紹的是如何生成一個庫的表結構，并通過該表結構反向生成表。

一 建立一個存儲表結構的表
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

上述方法不適合于復合索引。