批量表结构提取和批量建表
在进行系统设计和测试时，经常需要建立多库，并且各库内容相同。一般思路是导入或者是复制表的脚本来一个一个的建表，显然这样操作费时繁琐，而且不能保证索引等信息全部都一样。本文介绍的是如何生成一个库的表结构，并通过该表结构反向生成表。
一 建立一个存储表结构的表
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
上述方法不适合于复合索引。
