SQL SERVER 生成建表脚本

直接sql输出

存储过程方式生成

 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 @[email protected]_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 @[email protected]_cmd

    IF Len(@index_script) > 0
      SET @[email protected]_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