SQL Server dynamically creates table structure

Source: Internet
Author: User

The requirement is to design the table structure in Word (which looks very clear in Word, easy to maintain), and then copy the SQL inside the table that you want to dynamically create to store the table structure
CREATE TABLE [dbo].[sys_createtable](        [Serial Number] [int] IDENTITY(1,1) not NULL,        [Chinese name] [nvarchar]( -)NULL,        [English name] [nvarchar]( -)NULL,        [type, width, precision] [nvarchar]( -)NULL,        [Default value/Description] [nvarchar]( -)NULL,        [Table name] [nvarchar]( -)NULL,        [Table Chinese name] [nvarchar]( -)NULL,CONSTRAINT [pk_sys_createtable] PRIMARY KEY CLUSTERED (        [Serial Number] 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 PROCEDUREsp_sys_dynamiccreate asBEGIN                SETNOCOUNT on; Declare @sql nvarchar(Max)                Set @sql = "'                ; withNewTable as (                        SelectTable name, Chinese name fromSys_createtableGroup  bytable name, Chinese name)Select  @sql = @sql +  Case  when  Number = 1  Then '); --Drop Table'+Table name+'; CREATE TABLE'+Table name+' (                  ' Else "' End  + (                 Case  when  Number = 1  Then '         ['+English name+'] '+[type, width, precision] + 'NULL' Else '                        ,['+English name+'] '+[type, width, precision] + 'NULL' End) from (                SelectRow_number () Over(PARTITION byA. Table nameORDER  bySerial numberASC) as  NumberB.*  fromNewTable asA Left JoinSys_createtable asB onA. Table name=B. Table name) asoSet @sql  =   Right(@sql,LEN(@sql)-2)+ ')'--Print @sql                Exec(@sql)END

SQL Server dynamically creates a table structure

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.