We help IT Professionals succeed at work.

List all user indexes and identify if asociated with a PK

Kelvin Sparks
on
Hello, I need to list all indexes in my user tables for my schema, and determine if they are associated with a primary key. I have looked in the usr_indexes and user_indexes for any clue as to whether it is associated with a PK or not.

What am I missing?


Kelvin
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012

Commented:
s elect constraint_name,index_owner,index_name from user_constraints where constraint_type = 'P'
Most Valuable Expert 2011
Top Expert 2012

Commented:
use all_constraints  or dba_constraints instead of user_constraints to look at other schemas
Most Valuable Expert 2011
Top Expert 2012
Commented:
you could combine them, pulling all indexes,  along with constraint name if it's associated with a primary key

SELECT c.constraint_name, i.*
  FROM     user_indexes i
       LEFT JOIN
           user_constraints c
       ON i.index_name = c.index_name AND c.index_owner = USER AND c.constraint_type = 'P'
OK, thanks. Really useful - what I really need is the reverse of your last comment - all indexes at are NOT associated with a primary key. Table name and Index name.

Can ask as a new question if you want.

Kelvin
Thanks for help, was able to resolve it from here