select COLIDX.column_id col_seq, /* 1. Column Seq */
COLCMT.column_name field_nm, /* 2. Field Name */
'' old_column, /* 3. Old Column */
case COLIDX.data_type
when 'NUMBER'
then COLIDX.data_type || '('
|| COLIDX.data_precision || ','
|| COLIDX.data_scale ||')'
else
COLIDX.data_type || '('
|| COLIDX.data_length ||')' end data_type, /* 4. Field Data Type */
TBLPK.column_length length, /* 5. Length */
case COLIDX.nullable
when 'Y'
then ''
else
'NN' end null_able, /* 6. Null Enable */
TBLPK.column_position || '(' || TBLPK.index_name ||')' pk_name, /* 7. Primary Key(Primary_Name) */
COLIDX.data_default data_default, /* 8. Filed Default Values */
COLCMT.comments comments /* 9. Field Comment */
from ALL_COL_COMMENTS COLCMT,
ALL_TAB_COLS COLIDX,
(select column_position,
index_name,
table_name,
column_name,
column_length
from ALL_IND_COLUMNS
where index_name = 'PK_' || table_name and table_name = upper('OPIEF202H')) TBLPK
where COLIDX.table_name = upper('OPIEF202H')
and COLCMT.table_name = COLIDX.table_name
and COLIDX.column_name = TBLPK.column_name(+)
and COLCMT.column_name = COLIDX.column_name
order by COLIDX.column_id |