Narasimha Maiya Narasimha Maiya - 2 months ago 10
SQL Question

List the index which are not created by primary key

I need a query which will display all the Indexes present in the schema except those which are created when a primary key is created.
I tried with

select * from all_indexes
it will list all the index.

Answer

to display the indexes of the current db-user you can query user_indexes and user_constraints

select index_name from user_indexes
minus
select  index_name from user_constraints where constraint_type = 'P';

to display the indexes of the all schemas use the all_... dictionary views

select owner, index_name from all_indexes
minus
select owner, index_name from all_constraints where constraint_type = 'P';
Comments