SQL Server 动态创建表结构

zsy001

需求是，在word里面设计好表结构（主要在word中看起来一目了然，方便维护），然后复制sql 里面，希望动态创建出来
存储表结构的表


CREATE TABLE [dbo].[Sys_CreateTable](
[序号] [int] IDENTITY(1,1) NOT NULL,
[中文名] [nvarchar](500) NULL,
[英文名] [nvarchar](500) NULL,
[类型、宽度、精度] [nvarchar](500) NULL,
[默认值/描述] [nvarchar](500) NULL,
[表名] [nvarchar](500) NULL,
[表中文名] [nvarchar](500) NULL,
CONSTRAINT [PK_Sys_CreateTable] PRIMARY KEY CLUSTERED
(
[序号] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


CREATE PROCEDURE SP_SYS_DynamicCreate
AS
BEGIN
SET NOCOUNT ON;

Declare @sql nvarchar(max)
Set @sql = ''
;WITH  NewTable As (
Select 表名,表中文名 From Sys_CreateTable Group By 表名,表中文名
)
Select  @sql = @sql + Case When number = 1 Then ');
-- Drop Table '+表名+';
CREATE TABLE '+表名+' (
' Else '' End  + (
Case When number = 1 Then '         ['+英文名+'] '+[类型、宽度、精度] + ' NULL' Else '
,['+英文名+'] '+[类型、宽度、精度] + ' NULL' End) From (
Select  row_number()OVER(PARTITION BY a.表名 ORDER BY 序号 asc) As number,b.* From NewTable As A
Left Join Sys_CreateTable As b On a.表名 = b.表名
) As o
Set @sql  =  Right(@sql,LEN(@sql) -2 ) + ')'
--Print @sql
Exec (@sql)

END