list index and column names for a table again.sql
— ind_lst.sql
— mdw – list of all indexes and columns for given table_name start
clear breaks
clear col
col ind_own form a8 wrap
col ind_name form a15 wrap
col tab_own form a8 wrap
col tab_name form a12 wrap
col ind_type form a8 wrap
col unq     form a4 trunc
col ts_name form a8 wrap
col status  form a5 trunc
set pause on
set pause ‘Any Key…>’
set array 1
set pages 24
break on ind_owner nodup on ind_name nodup on tab_name skip 1
spool ind_lst.lst
 select
 OWNER                ind_own
,INDEX_NAMEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â ind_Name
,TABLE_owner                tab_own
,TABLE_NAMEÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â tab_Name
,tablespace_name            ts_name
,index_type                 ind_type
,uniqueness                 unq
,partitioned                part
,status
from dba_indexes
where table_name like upper(nvl(‘&tab_name’,’WHOOPS’)||’%’)
order by 3,1,2,4,5
/
spool off
Discussion ¬