Find indexes and assigned columns for a table



Last updated on July 17th, 2015 at 06:05 pm

It is very common that you want to know / see the available index on a table.
And also you sometime want to know the columns which are assigned to the
indexes.
Following views would be helpful in these cases
dba_ind_columns
all_ind_columns

set pagesize 50000 verify off echo off

col table_name head ‘Table Name’ format a20
col index_name head ‘Index Name’ format a25
col column_name head ‘Column Name’ format a30

break on table_name on index_name

select    table_name, index_name, column_name
from    all_ind_columns
where    table_name like upper(‘&Table_Name’)
order by table_name, index_name, column_position
/


Leave a Reply