SQL SERVER 生成建表脚本

最新推荐文章于 2023-05-30 16:35:13 发布

.NET跨平台

最新推荐文章于 2023-05-30 16:35:13 发布

阅读量943

点赞数

分类专栏： SqlServer数据库

SqlServer数据库专栏收录该内容

192 篇文章 34 订阅

订阅专栏

直接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