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