/*-----------------------------------------------------------------------------
* Function : to extract the structure of a table *
* Author : Richard Wang *
* Date : 2011-08-30 *
* Version : 0.1 *
------------------------------------------------------------------------------*/
select 'Table_Name' table_name
--,'Column_ID' column_id
,0 column_id
,'Column_Name' column_name
,'Data_Type' data_Type
,'PK_Column' PK_Column
,'Nullable' nullable
from DUAL
union all
select distinct
lower(all_c.table_name) table_name
--,(case
-- when column_id < 10 then '0' || to_char(column_id)
-- when column_id < 100 then to_char(column_id)
-- when column_id > 100 then 'H' || substrb(to_char(column_id),2,1)
-- else '**'
-- end ) column_id
,all_c.column_id column_id
,lower(all_c.column_name) column_name
,lower((case all_c.data_type
when 'NUMBER'
then all_c.data_type || decode(all_c.data_precision,0,'',NULL,'','(' || to_char(all_c.data_precision)) || decode(all_c.data_precision,0,'',NULL,'', decode(all_c.data_scale,0,'',NULL,'', ',' || to_char(all_c.data_scale)) || ')')
when 'CHAR'
then all_c.data_type || '(' || to_char(all_c.char_length) || ')'
when 'VARCHAR2'
then all_c.data_type || '(' || to_char(all_c.char_length) || ')'
else all_c.data_type
end)) data_type
,decode(tab_pk.pk_col_name,NULL,'',tab_pk.pk_col_name,'Y','N/A') PK_column
,all_c.nullable nullable
from all_tab_columns all_c
left outer join (select cu.table_name pk_tab_name
,cu.column_name pk_col_name
from user_cons_columns cu,
user_constraints au
where cu.constraint_name = au.constraint_name
and au.constraint_type = 'P') tab_pk
on all_c.table_name = tab_pk.pk_tab_name
and all_c.column_name = tab_pk.pk_col_name
where owner not like '%SYS%'
and table_name = upper('V_CHL_PORTFOLIO') -- upper case--
order by table_name,column_id,column_name
select * from all_tab_columns
where table_name = 'CIS_INF_PARM_VALUE'
select distinct
table_name
from all_tab_columns
where owner not like '%SYS%'
and table_name like '%DELIV%' -- upper case--
order by table_name
