Find primary key index

This tip is useful in locating the index name for a corresponding primary key constraint. It displays the details of the primary key along with the corresponding index name for a given owner and tablename. This script has been tested on 7x, 8x and 9x, and works on versions 8x or higher without any modification. Version 7.3.x c.validated column selections have to be commented as this column is not available in the dba_constraints table of this version.

set echo off
set verify off
set linesize 150
set pages 2000
undefine owner
undefine table_name

col c1 format a10 heading "Owner"
col c2 format a20 heading "Cons Name"
col c3 format a15 heading "Index Name"
col c4 format a4 heading "Type"
col c5 format a20 heading "Table Name"
col c6 format a8 heading "Status"
col c7 format a12 heading "Validation"

select  c.owner c1,
c.constraint_name c2,
i.index_name c3,
c.constraint_type c4,
c.table_name c5,
c.status c6,
c.validated c7
from  dba_constraints c,
dba_ind_columns ic,
dba_cons_columns cc,
dba_indexes i
WHERE c.OWNER=UPPER('&OWNER') 
AND c.TABLE_NAME=UPPER('&TABLE_NAME')
and c.constraint_type='P'
and cc.column_name=ic.column_name
and c.constraint_name=cc.constraint_name
and ic.index_name=i.index_name
and i.uniqueness='UNIQUE'
and c.table_name=ic.table_name
and ic.table_name=cc.table_name
and cc.table_name=i.table_name
and ic.table_name=i.table_name
/
undefine owner
undefine table_name 
clear columns 
clear breaks 
ttitle off 
set feed on 
SET VER ON 
SET ECHO ON

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.