SqlServer自动生成Hive建表语句

 create table T_SQL
(
  scsj       DATE,
  sqlyj      text,
  table_name VARCHAR(100) not null,
  mxyj       text
)

 create table T_P_TABLE_NAME_TEMP
 (
   table_name   VARCHAR(30) not null,
   column_name  VARCHAR(30) not null,
   data_type    VARCHAR(106),
 );

（注意修改数据库名）

create PROCEDURE   [数据库名].[P_DDPT_SQL_INIT]
@p_table_name AS varchar(max), --输入表名,必须大写
@p_error_no   AS int, --错误编号
@p_error_info AS varchar  --错误信息
                                           

--调度平台 建表语句生成
AS 
  DECLARE @nb_begindate date;
  DECLARE @nb_enddate   date;
  DECLARE @v_count      int;
  DECLARE @v_update_sql varchar(max);
  DECLARE @v_temp_sql varchar(max);
  DECLARE @v_detail_sql varchar(max);
  DECLARE @v_out_sql varchar(max);
  DECLARE @v_owner varchar(2000);
  DECLARE @v_table_name varchar(2000);
  DECLARE @v_tab_comments varchar(2000);
  DECLARE @v_column_name varchar(2000);
  DECLARE @v_data_type varchar(2000);
  DECLARE @v_col_comments varchar(2000);
	declare @temp varchar(50)
BEGIN
print @p_table_name;
BEGIN TRY
 --print '12345';
	set @p_error_no   = 0;
  set @p_error_info = '';
  set @nb_begindate = CONVERT(varchar,GETDATE(),120) ;

  set @v_out_sql= 'use 数据库名;'+CHAR(10) +'drop table '+@p_table_name+';'+CHAR(10) +'create table '+@p_table_name+'('+CHAR(10);

  EXEC('truncate table t_p_table_name_temp')
-- print '13';
 insert into t_p_table_name_temp
(table_name, column_name, data_type)
select table_name,column_name,data_type
from information_schema.columns
where 
table_name= @p_table_name
--table_name= 'ADDRESSBOOK'
ORDER BY ORDINAL_POSITION;

-- print @p_table_name;
 

   update t_p_table_name_temp set data_type = 'string' where data_type = 'nvarchar';
	 update t_p_table_name_temp set data_type = 'string' where data_type = 'char';
	 update t_p_table_name_temp set data_type = 'string' where data_type = 'text';
   update t_p_table_name_temp set data_type = 'double' where data_type = 'float';
	 update t_p_table_name_temp set data_type = 'boolean' where data_type = 'bit';
	 update t_p_table_name_temp set data_type = 'string' where data_type = 'datetime';
	 update t_p_table_name_temp set data_type = 'string' where data_type = 'numeric';


 --print @p_table_name;
   --dbms_output.put_line(v_update_sql);

DECLARE c_row cursor for (SELECT column_name FROM t_p_table_name_temp)
  -- for c_row in (SELECt * FROM t_p_table_name_temp)
--   loop
open c_row
 fetch next from c_row into @temp
 while @@FETCH_STATUS = 0
 begin
      --@v_owner = c_row.owner;
     set @v_table_name = @p_table_name;
	--	print  @v_table_name
     -- @v_tab_comments = c_row.tab_comments;
     set @v_column_name = @temp;
     set @v_data_type = (select data_type from t_p_table_name_temp where column_name=@temp);
    --  @v_col_comments = c_row.col_comments;

      if (@v_temp_sql is null) 
			begin
        set @v_temp_sql = @v_column_name +' '+@v_data_type;
         set @v_detail_sql = @v_column_name;
				 end
				 
      else
			begin
        set @v_temp_sql = @v_temp_sql +','+CHAR(10) + @v_column_name +' '+@v_data_type;
        set @v_detail_sql = @v_detail_sql+',' + CHAR(10)+ @v_column_name;
				 end
fetch next from c_row into @temp
 -- end loop;
end
close c_row
DEALLOCATE  c_row
  set @v_out_sql = @v_out_sql + @v_temp_sql +') '+CHAR(10);
  set @v_detail_sql = 'select ' + CHAR(10)+ @v_detail_sql + ' from '+@v_table_name;
  set @v_out_sql=@v_out_sql+'ROW FORMAT DELIMITED FIELDS TERMINATED BY '',''
STORED AS ORC ;';

 --print @p_table_name;

  set @nb_enddate = CONVERT(varchar,GETDATE(),120) ;

  delete t_sql where table_name = @p_table_name;
  insert into t_sql
    (scsj, sqlyj, table_name, mxyj)
  values
    (@nb_enddate, @v_out_sql, @p_table_name, @v_detail_sql);


 set @p_error_no   = 1;
 set @p_error_info = '调度平台建表语句生成成功！';
 
END TRY
     BEGIN CATCH
  set  @p_error_no   = -1;
  set  @p_error_info = '调度平台建表语句生成失败！' ;

		END CATCH
END

exec 数据库名.P_DDPT_SQL_INIT @p_table_name='edoc_body',@p_error_no=0,@p_error_info='';
exec 数据库名.P_DDPT_SQL_INIT @p_table_name='formson_0152',@p_error_no=0,@p_error_info='';
exec 数据库名.P_DDPT_SQL_INIT @p_table_name='CTP_ENUM_CN',@p_error_no=0,@p_error_info='';
exec 数据库名.P_DDPT_SQL_INIT @p_table_name='表名',@p_error_no=0,@p_error_info='';

表名可通过以下sql语句查询：

select table_name 

from information_schema.tables 

where table_schema='数据库名'

注意T_SQL和T_P_TABLE_NAME_TEMP是多余的表明，记得删除