CODEWITHSUNDEEP
sqloracle-database
Narasimha Maiya
Narasimha Maiya

Reputation: 1029

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.

Upvotes: 1

Views: 221

Answers (1)

schurik
schurik

Reputation: 7928

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';

Upvotes: 5

Related Questions