Narasimha Maiya Narasimha Maiya - 1 year ago 65
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 Source

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

select index_name from user_indexes
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
select owner, index_name from all_constraints where constraint_type = 'P';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download