Generate Creation Table Script using SQL Query

SQL Query

select 'create table ' || table_name || ' (' || column_name || ' ' || 

DATA_TYPE

|| '(' || 

case data_type 

when 'NUMBER' then data_precision

when 'VARCHAR2' then CHAR_LENGTH

when 'CHAR' then CHAR_LENGTH

end

 || '),' txt,table_name,column_id,data_type from user_tab_columns where table_name in (

 'EMPLOYEE',

'EMPLOYEE_POSITION'

 ) and column_id=1

union

select column_name || ' ' || 

DATA_TYPE

|| '(' || 

case data_type 

when 'NUMBER' then data_precision

when 'VARCHAR2' then CHAR_LENGTH

when 'CHAR' then CHAR_LENGTH

end

|| '),' txt,table_name,column_id,data_type from user_tab_columns out1 where table_name in (

 'EMPLOYEE',

'EMPLOYEE_POSITION'

) and column_id>1 and column_id<(

select max(column_id) from user_tab_columns where table_name=out1.table_name

)

union

select column_name || ' ' || 

DATA_TYPE

|| '(' || 

case data_type 

when 'NUMBER' then data_precision

when 'VARCHAR2' then CHAR_LENGTH

when 'CHAR' then CHAR_LENGTH

end

|| '));' txt,table_name,column_id,data_type from user_tab_columns out1 where table_name in (

 'EMPLOYEE',

'EMPLOYEE_POSITION'

) and column_id=(

select max(column_id) from user_tab_columns where table_name =out1.table_name

)

) order by table_name,column_id

Result

create table EMPLOYEE (EMPLOYEE_NO NUMBER(10),

EMPLOYEE_FIRST_NAME VARHCAR(30),

EMPLOYEE_LAST_NAME VARHCAR(30),

ADDRESS VARCHAR(100),

UPDATE_DATETME DATE);

create table EMPLOYEE_POSITION (EMPLOYEE_NO NUMBER(10),

COST_CENTRE VARCHAR(10),

UNIT VARCHAR(10),

ROLE VARCHAR(10),

UPDATE_DATETIME DATE);