oracle建表简单语句,获得oracle建表语句

/*

*/

with ora2hive_tabl_name as

(select

aat.table_name

from all_all_tables aat

where aat.owner in ('OWNER')

and aat.table_name in ('TABLE_NAME'

)

)

select

aat.owner,

aat.table_name,

0 column_id,

'create table if not exists '||rpad(lower(aat.table_name||'_f'),32,' ')||'(' msg

from all_all_tables aat,

ora2hive_tabl_name ora2hive

where aat.table_name =ora2hive.table_name

union all

select

atc.owner,

atc.table_name,

atc.column_id,

rpad( lower(atc.column_name),30,' ')

||rpad(decode(atc.data_type,'DATE','string','string'),8,' ')

|| ' comment '

||rpad(''''    || replace(acc.comments,';',' ')    || '''' ,110,' ')

|| (case when atc.column_id=atc_max.max_column_id then ' ' else  ',' end ) msg

from all_tab_columns atc,

ora2hive_tabl_name ora2hive,

all_col_comments acc,

(select atc2.table_name,max(atc2.column_id) max_column_id from all_tab_columns atc2 where atc2.OWNER like '%OWNER' group by atc2.table_name) atc_max

where atc.table_name = ora2hive.table_name

and atc.table_name=acc.table_name

and atc.column_name=acc.column_name

and atc.table_name=atc_max.table_name

union all

select

aac.owner,

aac.table_name,

99 as column_id,

') comment '||''''||aac.COMMENTS||''''||' partitioned by(op_day string comment ''数据导入时间'')  row format delimited fields terminated by ''\001'' stored as textfile;'

from all_tab_comments  aac,

ora2hive_tabl_name ora2hive

where aac.table_name=ora2hive.table_name

order by owner,table_name,column_id;

with ora2hive_tabl_name as

(select

aat.table_name

from all_all_tables aat

where aat.owner in ('OWNER')

and aat.table_name in ('TABLE_NAME'

)

)

select

a.TABLE_NAME,

'1' id,

'db_columns='||max(msg) as msg

from

(

select

atc.TABLE_NAME,

lower((TO_CHAR(WMSYS.WM_CONCAT(case when ATC.COLUMN_NAME in ('UPDATE_TIME') then 'nvl('||ATC.COLUMN_NAME||','||'CREATE_TIME) as '||ATC.COLUMN_NAME

when ATC.COLUMN_NAME in ('UPDATE_USER') then 'nvl('||ATC.COLUMN_NAME||','||'CREATE_USER) as '||ATC.COLUMN_NAME

else ATC.COLUMN_NAME

end

)

OVER(PARTITION BY atc.TABLE_NAME ORDER BY ATC.COLUMN_ID)))) msg

from all_tab_columns      atc,

ora2hive_tabl_name  ora2hive

where atc.TABLE_NAME=ora2hive.TABLE_NAME

) a

group by a.TABLE_NAME

union all

select

aat.TABLE_NAME,

'2' id,

'db_table_name='||lower(aat.owner)||'.'||lower(aat.TABLE_NAME) msg

from

all_all_tables aat,

ora2hive_tabl_name ora2hive

where aat.TABLE_NAME=ora2hive.TABLE_NAME

union all

select

aat.TABLE_NAME,

'3' id,

'hive_table='||lower(aat.TABLE_NAME||'_f') msg

from

all_all_tables aat,

ora2hive_tabl_name ora2hive

where aat.TABLE_NAME=ora2hive.TABLE_NAME

order by TABLE_NAME,id;