SQL SERVER 生成建表脚本（sql通过存储过程获取创建表脚本）

网上抄回来改进的，改进增加一个扩展字段的指定，用于生成的脚本可以改变表名称，而不用和原来的表一样。应用于分表时自动创建表的场景。

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140

USE [Ctrl.Js] GO /****** Object:  StoredProcedure [dbo].[GET_TableScript_MSSQL]    Script Date: 09/17/2018 17:51:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /*============================================================== 名称: GET_TableScript_MSSQL 功能: 获取customize单个表的mysql脚本 创建：2010年5月12日 参数：@DBNAME   --数据库名称       @TBNAME   --表名       @TBNAME_ext   --可选项，留空表示原样生成脚本，非空，表示生成的脚本在原表名称后面加上后缀，同时约束、索引的名称也会以相同的方式加上后缀。       @SQL      --输出脚本 ==============================================================*/ ALTER PROCEDURE [dbo].[GET_TableScript_MSSQL] (     @DBNAME varchar(40),     @TBNAME VARCHAR(100),     @TBNAME_ext varchar(100)='',     @SQL VARCHAR(max) OUTPUT ) AS                     declare @table_script nvarchar(max) --建表的脚本 declare @index_script nvarchar(max) --索引的脚本 declare @default_script nvarchar(max) --默认值的脚本 declare @check_script nvarchar(max) --check约束的脚本 declare @sql_cmd nvarchar(max)  --动态SQL命令 declare @err_info varchar(200) --SET @tbname = UPPER(@tbname); if OBJECT_ID(@DBNAME+'.dbo.'+@TBNAME) is null BEGIN     set @err_info='对象:'+@DBNAME+'.dbo.'+@TBNAME+'不存在!'     raiserror(@err_info,16,1)     return END ----------------------生成创建表脚本---------------------------- --1.添加算定义字段 set @table_script = 'CREATE TABLE '+@TBNAME+@TBNAME_ext+' ('+char(13)+char(10);       --添加表中的其它字段 set @sql_cmd=N' use '+@DBNAME+' set @table_script='''' select @table_script=@table_script+         '' [''+t.NAME+''] ''         +(case when t.xusertype in (175,62,239,59,122,165,173) then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'')''               when t.xusertype in (231) and t.length=-1 then ''[ntext]''               when t.xusertype in (231) and t.length<>-1 then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'')''              when t.xusertype in (167) and t.length=-1 then ''[text]''               when t.xusertype in (167) and t.length<>-1 then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'')''               when t.xusertype in (106,108) then ''[''+p.name+''] (''+convert(varchar(30),isnull(t.prec,''''))+'',''+convert(varchar(30),isnull(t.scale,''''))+'')''               else ''[''+p.name+'']''          END)          +(case when t.isnullable=1 then '' null'' else '' not null ''end)          +(case when COLUMNPROPERTY(t.ID, t.NAME, ''ISIDENTITY'')=1 then '' identity'' else '''' end)          +'',''+char(13)+char(10) from syscolumns t join systypes p  on t.xusertype = p.xusertype where t.ID=OBJECT_ID('''+@TBNAME+''') ORDER BY  t.COLID; ' EXEc sp_executesql @sql_cmd,N'@table_script varchar(max) output',@sql_cmd output set @table_script=@table_script+@sql_cmd IF len(@table_script)>0     set @table_script=substring(@table_script,1,len(@table_script)-3)+char(13)+char(10)         +')'+char(13)+char(10)         +' '+char(13)+char(10)+char(13)+char(10)       --------------------生成索引脚本--------------------------------------- set @index_script='' set @sql_cmd=N' use '+@DBNAME+' declare @ct int declare @indid int      --当前索引ID declare @p_indid int    --前一个索引ID select @indid=-1, @p_indid=0,@ct=0    --初始化，以后用@indid和@p_indid判断是否索引ID发生变化 set @index_script='''' select @indid=INDID     ,@index_script=@index_script     +(case when @indid<>@p_indid and @ct>0 then '')''+char(13)+char(10)+'' ''+char(13)+char(10) else '''' end)     +(case when @indid<>@p_indid and UNIQ=''PRIMARY KEY''           then ''ALTER TABLE ''+TABNAME+'''+@TBNAME_ext+' ADD CONSTRAINT ''+name+'''+@TBNAME_ext+' PRIMARY KEY ''+cluster+char(13)+char(10)                 +''(''+char(13)+char(10)                 +''    ''+COLNAME+char(13)+char(10)           when @indid<>@p_indid and UNIQ=''UNIQUE''           then ''ALTER TABLE ''+TABNAME+'''+@TBNAME_ext+' ADD CONSTRAINT ''+name+'''+@TBNAME_ext+' UNIQUE ''+cluster+char(13)+char(10)                 +''(''+char(13)+char(10)                 +''    ''+COLNAME+char(13)+char(10)           when @indid<>@p_indid and UNIQ=''INDEX''               then ''CREATE ''+cluster+'''+@TBNAME_ext+' INDEX ''+name+'''+@TBNAME_ext+' ON ''+TABNAME+'''+@TBNAME_ext+'''+char(13)+char(10)                 +''(''+char(13)+char(10)                 +''    ''+COLNAME+char(13)+char(10)           when @indid=@p_indid           then  ''    ,''+COLNAME+char(13)+char(10)      END)     ,@ct=@ct+1     ,@p_indid=@indid from (     SELECT A.INDID,B.KEYNO         ,NAME,(SELECT NAME FROM SYSOBJECTS WHERE ID=A.ID) AS TABNAME,         (SELECT NAME FROM SYSCOLUMNS WHERE ID=B.ID AND COLID=B.COLID) AS COLNAME,         (CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=''UQ'') THEN ''UNIQUE''               WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE NAME=A.NAME AND XTYPE=''PK'') THEN ''PRIMARY KEY''               ELSE ''INDEX'' END)  AS UNIQ,         (CASE WHEN A.INDID=1 THEN ''CLUSTERED'' WHEN A.INDID>1 THEN ''NONCLUSTERED'' END) AS CLUSTER     FROM SYSINDEXES A INNER JOIN SYSINDEXKEYS B ON A.INDID=B.INDID AND A.ID=B.ID     WHERE A.ID=OBJECT_ID('''+@TBNAME+''') and a.indid<>0 ) t ORDER BY INDID,KEYNO' EXEc sp_executesql @sql_cmd,N'@index_script varchar(max) output',@sql_cmd output set @index_script=@sql_cmd IF len(@index_script)>0     set @index_script=@index_script+')'+char(13)+char(10)+' '+char(13)+char(10)+char(13)+char(10) --生成默认值约束 set @sql_cmd=' use '+@DBNAME+' set @default_script='''' SELECT @default_script=@default_script         +''ALTER TABLE ''+OBJECT_NAME(O.PARENT_OBJ)+'''+@TBNAME_ext+'''         +'' ADD CONSTRAINT ''+O.NAME+'''+@TBNAME_ext+' default ''+t.text+'' for ''+C.NAME+char(13)+char(10)         +'' ''+char(13)+char(10) FROM SYSOBJECTS O INNER JOIN SYSCOMMENTS T ON O.ID=T.ID     INNER JOIN SYSCOLUMNS C ON O.PARENT_OBJ=C.ID AND C.CDEFAULT=T.ID WHERE O.XTYPE=''D'' AND O.PARENT_OBJ=OBJECT_ID('''+@TBNAME+''')' EXEc sp_executesql @sql_cmd,N'@default_script varchar(max) output',@sql_cmd output set @default_script=@sql_cmd+char(13)+char(10)   set @SQL=@table_script+@index_script+@default_script declare @len int,@n int set @len=LEN(@SQL) set @n=0 while(@len>0) BEGIN   PRINT(substring(@SQL,@n*4000+1,4000));   set @n=@n+1   set @len=@len-4000; END

　　

示例：

?

1 2 3

DECLARE @SQL NVARCHAR(3000) EXEC GET_TableScript_MSSQL 'BusinessSystemDB','Swb_Yw_KeHuBase','_2021',@SQL OUT SELECT @SQL