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.
Dig Deeper on Oracle and SQL
Computacenter pays £500k for minority stake in ICS Solutions
UNIQUE index vs. PRIMARY KEY constraint to rebuild Oracle indexes and constraints
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 5
IBM DB2 9 Fundamentals certification (Exam 730): Sample questions about tables, Part 3