SQL SERVER 生成建表脚本

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

## 直接sql输出 ##

SET ansi_nulls ON 
    SET quoted_identifier ON 
    
    DECLARE @DBNAME VARCHAR(40), 
            @TBNAME VARCHAR(100), 
            @SQL    VARCHAR(max) 
    
    SET @DBNAME='O2Odb'; 
    SET @TBNAME='order'; 
    
    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 
                        + ' (' + 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) + 'GO' + 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)+''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(''' 
                 + @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', 
      @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)); 
          SELECT @SQL; 
    
          SET @n=@n + 1 
          SET @len=@len - 4000; 
      END

## 存储过程方式生成 ##

SET ansi_nulls ON
    
    go
    
    SET quoted_identifier ON
    
    go
    
    /*============================================================== 名称: GET_TableScript_MSSQL 功能: 获取customize单个表的mysql脚本 创建：2010年5月12日 参数：@DBNAME --数据库名称 @TBNAME --表名 @SQL --输出脚本 ==============================================================*/
    ALTER PROCEDURE [dbo].[Get_tablescript_mssql] (@DBNAME VARCHAR(40),
                                                   @TBNAME 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 + ' (' + 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) + 'GO' + 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)+''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('''
                     + @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',
          @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