[oracle] how to find all indexes and their columns for tables, views and synonyms in oracle

I jotted down the following query which will list the index name and its columns for a particular table:

select 
b.uniqueness, a.index_name, a.table_name, a.column_name 
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name 
and a.table_name = upper('table_name')
order by a.table_name, a.index_name, a.column_position;

I want to modify this so that if I pass in a view or synonym also it works. Our system has variations of views, synonyms so it will be really helpful to have one query to which i can just supply the name (be it view synonym or table) and it would spit out the indexes and their columns.

This question is related to oracle

The answer is


Your query should work for synonyms as well as the tables. However, you seem to expect indexes on views where there are not. Maybe is it materialized views ?


SELECT * FROM user_cons_columns WHERE table_name = 'table_name';