SQL Server 動態建立表結構

標籤：

需求是，在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_DynamicCreateASBEGIN                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

 

SQL Server 動態建立表結構