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
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';
Source: Stackoverflow.com