Table & Index Info SQL


vi indexdesc.sql

set heading on
set pagesize 66
set linesize 500

column tab_name format a15 heading 'Table'
column num_rows format 9999999 heading '# Rows'
column ind_name format a19 heading 'Index'
column uniqueness format a1 heading 'U'
column col_name format a15 heading 'Column'
column col_position format 99 heading 'Pos'
column col_length format 999 heading 'Len'
column num_distinct format 9999999 heading '# Dist'
break on tab_name skip 1 on ind_name on uniqueness on num_rows
ttitle '&owner''s Indexes'

spool tblidx

select        x.table_name tab_name,
        x.num_rows num_rows,
        a.index_name ind_name,
        decode(a.uniqueness, 'UNIQUE', 'U', 'N') uniqueness,
        b.column_name col_name,
        b.column_position col_position,
        b.column_length col_length,
        y.num_distinct num_distinct
from        dba_tables x, dba_tab_columns y, dba_indexes a, dba_ind_columns b
where        x.owner = upper('&owner')
and        a.table_owner = x.owner
and        a.table_owner = b.index_owner
and        x.table_name = a.table_name
and        a.table_name = b.table_name
and        a.index_name = b.index_name
and        x.owner = y.owner
and        x.table_name = y.table_name
and        b.column_name = y.column_name
/
spool off
set verify on
set echo on
set feedback on
exit