Find indexes and assigned columns for a table

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.

The view dba_ind_columns or user_ind_columns or all_ind_columns help lot in this regard. As you may know dba_* show all available index information, user_* is for the available in current schema and all_* is for all indexes that current user has permission to it.

The following script will help to identify the owner, table_name associated index and associated column.

SQL>COL index_owner FORMAT A20
column table_owner format a30
column table_name format A28
column index_name format A28
column column_name format A28
COL POS FORMAT 999

SQL> Select index_owner, table_name, index_name, column_name, column_position POS
FROM dba_ind_columns
Where index_owner='&owner_name'
AND table_name='&table_name' Order by index_NAME;

In order to know the avilable index on a table issue,

SQL> SELECT UNIQUE INDEX_NAME FROM dba_ind_columns WHERE TABLE_NAME='&TABLE_NAME';

Example:
------------
SQL> create table test_index_col( a number primary key, b number);
Table created.


SQL> create index test_I on test_index_col(b);
Index created.

SQL> Select index_owner, table_name, index_name, column_name, column_position POS
FROM dba_ind_columns
Where index_owner='&owner_name'
AND table_name='&table_name' Order by index_NAME; 2 3 4
Enter value for owner_name: ARJU
old 3: Where index_owner='&owner_name'
new 3: Where index_owner='ARJU'
Enter value for table_name: TEST_INDEX_COL
old 4: AND table_name='&table_name' Order by index_NAME
new 4: AND table_name='TEST_INDEX_COL' Order by index_NAME

INDEX_OWNE TABLE_NAME INDEX_NAME COLUMN_NAME POS
---------- ------------------ ------------------ ------------------ ----
ARJU TEST_INDEX_COL SYS_C006341 A 1
ARJU TEST_INDEX_COL TEST_I B 1