SQL SERVER 生成建表脚本

ゝ一纸荒年。 2021-06-26 16:06 632阅读 0赞

直接sql输出

  1. SET ansi_nulls ON 
  2. SET quoted_identifier ON 
  4. DECLARE @DBNAME VARCHAR(40), 
  5.         @TBNAME VARCHAR(100), 
  6.         @SQL    VARCHAR(max) 
  8. SET @DBNAME='O2Odb'; 
  9. SET @TBNAME='order'; 
  11. DECLARE @table_script NVARCHAR(max) --建表的脚本 
  12. DECLARE @index_script NVARCHAR(max) --索引的脚本 
  13. DECLARE @default_script NVARCHAR(max) --默认值的脚本 
  14. DECLARE @check_script NVARCHAR(max) --check约束的脚本 
  15. DECLARE @sql_cmd NVARCHAR(max) --动态SQL命令 
  16. DECLARE @err_info VARCHAR(200) 
  18. SET @tbname = Upper(@tbname); 
  20. IF Object_id(@DBNAME + '.dbo.' + @TBNAME) IS NULL 
  21.   BEGIN 
  22.       SET @err_info='对象:' + @DBNAME + '.dbo.' + @TBNAME 
  23.                     + '不存在!' 
  25.       RAISERROR(@err_info,16,1) 
  27.       RETURN 
  28.   END 
  30. ----------------------生成创建表脚本---------------------------- 
  31. --1.添加算定义字段 
  32. SET @table_script = 'CREATE TABLE ' + @TBNAME 
  33.                     + ' (' + Char(13) 
  34.                     + Char(10); 
  35. --添加表中的其它字段 
  36. SET @sql_cmd=N' use ' + @DBNAME 
  37.              + ' 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(''' 
  38.              + @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) + 'GO' + Char(13) + Char(10) + Char(13) + Char(10) --------------------生成索引脚本--------------------------------------- SET @index_script='' 
  39. SET @sql_cmd=N' use ' + @DBNAME 
  40.              + ' 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)+''go''+char(13)+char(10) else '''' end) +(case when @indid<>@p_indid and UNIQ=''PRIMARY KEY''                                    then ''ALTER TABLE ''+TABNAME+'' ADD CONSTRAINT ''+name+'' 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+'' ADD CONSTRAINT ''+name+'' 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+'' INDEX ''+name+'' ON ''+TABNAME+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(''' 
  41.              + @TBNAME 
  42.              + ''') 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) + 'go' + 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)                                 +'' ADD CONSTRAINT ''+O.NAME+'' default ''+t.text+'' for ''+C.NAME+char(13)+char(10)                                 +''GO''+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(''' 
  43.              + @TBNAME + ''')' EXEC Sp_executesql @sql_cmd, N'@default_script varchar(max) output', 
  44.   @sql_cmd output 
  46. SET @default_script=@sql_cmd + Char(13) + Char(10) 
  47. SET @SQL=@table_script + @index_script 
  48.          + @default_script 
  50. DECLARE @len INT, 
  51.         @n   INT 
  53. SET @len=Len(@SQL) 
  54. SET @n=0 
  56. WHILE( @len > 0 ) 
  57.   BEGIN 
  58.       --PRINT(substring(@SQL,@n*4000+1,4000)); 
  59.       SELECT @SQL; 
  61.       SET @n=@n + 1 
  62.       SET @len=@len - 4000; 
  63.   END

存储过程方式生成

  1. SET ansi_nulls ON
  3. go
  5. SET quoted_identifier ON
  7. go
  9. /*============================================================== 名称: GET_TableScript_MSSQL 功能: 获取customize单个表的mysql脚本 创建：2010年5月12日 参数：@DBNAME --数据库名称 @TBNAME --表名 @SQL --输出脚本 ==============================================================*/
  10. ALTER PROCEDURE [dbo].[Get_tablescript_mssql] (@DBNAME VARCHAR(40),
  11.                                                @TBNAME VARCHAR(100),
  12.                                                @SQL    VARCHAR(max) output)
  13. AS
  14.     DECLARE @table_script NVARCHAR(max) --建表的脚本
  15.     DECLARE @index_script NVARCHAR(max) --索引的脚本
  16.     DECLARE @default_script NVARCHAR(max) --默认值的脚本
  17.     DECLARE @check_script NVARCHAR(max) --check约束的脚本
  18.     DECLARE @sql_cmd NVARCHAR(max) --动态SQL命令
  19.     DECLARE @err_info VARCHAR(200)
  21.     SET @tbname = Upper(@tbname);
  23.     IF Object_id(@DBNAME + '.dbo.' + @TBNAME) IS NULL
  24.       BEGIN
  25.           SET @err_info='对象:' + @DBNAME + '.dbo.' + @TBNAME
  26.                         + '不存在!'
  28.           RAISERROR(@err_info,16,1)
  30.           RETURN
  31.       END
  33.     ----------------------生成创建表脚本----------------------------
  34.     --1.添加算定义字段
  35.     SET @table_script = 'CREATE TABLE ' + @TBNAME + ' (' + Char(13) + Char(10);
  36.     --添加表中的其它字段
  37.     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('''
  38.                  + @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) + 'GO' + Char(13) + Char(10) + Char(13) + Char(10) --------------------生成索引脚本--------------------------------------- SET @index_script=''
  39.     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)+''go''+char(13)+char(10) else '''' end) +(case when @indid<>@p_indid and UNIQ=''PRIMARY KEY''            then ''ALTER TABLE ''+TABNAME+'' ADD CONSTRAINT ''+name+'' 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+'' ADD CONSTRAINT ''+name+'' 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+'' INDEX ''+name+'' ON ''+TABNAME+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('''
  40.                  + @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) + 'go' + 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)         +'' ADD CONSTRAINT ''+O.NAME+'' default ''+t.text+'' for ''+C.NAME+char(13)+char(10)         +''GO''+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',
  41.       @sql_cmd output
  43.     SET @default_script=@sql_cmd + Char(13) + Char(10)
  44.     SET @SQL=@table_script + @index_script
  45.              + @default_script
  47.     DECLARE @len INT,
  48.             @n   INT
  50.     SET @len=Len(@SQL)
  51.     SET @n=0
  53.     WHILE( @len > 0 )
  54.       BEGIN
  55.           PRINT( Substring(@SQL, @n * 4000 + 1, 4000) );
  57.           SET @n=@n + 1
  58.           SET @len=@len - 4000;
  59.       END

发表评论

表情：
评论列表 （有 0 条评论，632人围观）

还没有评论，来说两句吧...

相关阅读

    相关 SQL Server SQL脚本

    本节的主要内容是要教大家怎么通过编写 SQL 脚本来查询、更新并且运行数据库。 利用 SQL 脚本我们能做很多事情，比如插入数据、读取数据、更新数据以及删除数据等；它们也可以

    布满荆棘的人生布满荆棘的人生/ 0 赞/ 195 阅读

    相关 cmd 执行sql脚本 sql server

    工作中遇到，当使用Sql Server管理器生成数据库脚本，由于脚本文件太大，导致不能使用Sql Server管理器执行或者无法打开sql脚本，此时就可以使用命令行执行sql脚

    水深无声水深无声/ 0 赞/ 403 阅读

    相关 SpringBoot生成sql脚本

    前言 最近项目新增加了一个导入导出的功能，起因是因为项目需要迁移环境，项目的业务需要所有的页面都是可配置的，当我在测试环境配置好一套页面，并且测试无误后，需要把我配置好的

    古城微笑少年丶古城微笑少年丶/ 0 赞/ 461 阅读

    相关 SQL Server SQL脚本

    SQL Server SQL脚本 本节的主要内容是要教大家怎么通过编写 SQL 脚本来查询、更新并且运行数据库。 利用 SQL 脚本我们能做很多事情，比如插入数据、读取数...

    小灰灰小灰灰/ 0 赞/ 2589 阅读