Re: How to get the index name of the primary key ?

From: Daniel Clamage <dclamageNOSPAM_at_telerama.com>
Date: Sun, 29 Nov 1998 16:27:24 -0000
Message-ID: <3661bd9b.0@paperboy.telerama.com>

Here is a PL/SQL stored procedure you can run from SQL*Plus to look at all indexes defined for any table in any schema. Compile it as SYS. You could easily add a test against dba_tab_constraints to see if the index represents a primary or unique key constraint.
SET ECHO ON
CREATE OR REPLACE PROCEDURE get_index(

    Ptable_name IN dba_indexes.table_name%TYPE) IS /*

   Program Name: Get Index Information
   Module Name : getindex.sql
   Written By  : Daniel J. Clamage
   Description :

   This stored procedure displays index information    for any table in any schema. For each table,    the owner and tablespace is shown, and for each    index for that table, the index name, its uniqueness,    columns in the index along with their datatype,    length and nullability.
   NOTE: This module must be compiled by SYS.    EXAMPLE:
   EXEC GET_INDEX('DEPT');
   INDEX INFORMATION FOR TABLE: DEPT:
>>OWNER: SCOTT; TABLESPACE: USER_DATA
   ........ INDEX: PK_DEPT UNIQUE

   ............... DEPTNO                    NUMBER     (2,0)   NOT NULL

   Modification:
   V.001 27-AUG-1997 - djc - Initial Release.    V.002 08-JUL-1998 - djc - Increased enable, added drop synonym.. */

• global cursors
• finds one or more indexes for one specified table CURSOR main_index_cur(Ptable_name IN dba_indexes.table_name%TYPE) IS SELECT index_name, tablespace_name, owner, uniqueness FROM dba_indexes WHERE table_name = Ptable_name ORDER BY owner, index_name;
• finds one or more index columns for one specified index CURSOR index_column_cur( Ptable_name IN dba_ind_columns.table_name%TYPE, Pindex_name IN dba_ind_columns.index_name%TYPE, Powner IN dba_ind_columns.index_owner%TYPE) IS SELECT column_name FROM dba_ind_columns WHERE table_name = Ptable_name AND index_name = Pindex_name AND index_owner= Powner ORDER BY column_position;
• finds exactly one column CURSOR column_cur(Ptable_name IN dba_tab_columns.table_name%TYPE, Pcolumn_name IN dba_tab_columns.column_name%TYPE, Powner IN dba_tab_columns.owner%TYPE) IS SELECT data_type, data_length, data_precision, data_scale, nullable FROM dba_tab_columns WHERE owner = Powner AND table_name = Ptable_name AND column_name = Pcolumn_name;
• global variables main_index_rec main_index_cur%ROWTYPE; -- record variables index_column_rec index_column_cur%ROWTYPE; column_rec column_cur%ROWTYPE; status NUMERIC; -- trap exception old_owner dba_indexes.owner%TYPE := 'garbage'; -- look for control break index_cnt NUMERIC := 0; utable_name dba_indexes.table_name%TYPE; null_col VARCHAR2(8);

BEGIN
  DBMS_OUTPUT.enable(1000000);
  utable_name := UPPER(Ptable_name); -- convert table name to upper case   DBMS_OUTPUT.put_line('INDEX INFORMATION FOR TABLE: ' || utable_name || ':');
  OPEN main_index_cur(utable_name);
  LOOP -- through all indexes for a table; handles same table different owners

    FETCH main_index_cur INTO main_index_rec;     EXIT WHEN main_index_cur%NOTFOUND;
    index_cnt := index_cnt + 1; -- found an index     IF (main_index_rec.owner != old_owner) THEN -- print this once

      old_owner := main_index_rec.owner;         -- set for next row
      DBMS_OUTPUT.put_line('>>OWNER: ' || main_index_rec.owner ||
                           '; TABLESPACE: ' ||

main_index_rec.tablespace_name);

    END IF; -- control break
    DBMS_OUTPUT.put_line('........ INDEX: ' || main_index_rec.index_name ||

                         '  ' || main_index_rec.uniqueness);
    OPEN index_column_cur(utable_name, main_index_rec.index_name,
                          main_index_rec.owner);
    LOOP
      FETCH index_column_cur INTO index_column_rec;
      EXIT WHEN index_column_cur%NOTFOUND;
      OPEN column_cur(utable_name, index_column_rec.column_name,
                      main_index_rec.owner);
      FETCH column_cur INTO column_rec;
      CLOSE column_cur;
      IF column_rec.nullable = 'N' THEN
        null_col := 'NOT NULL';
      ELSE
        null_col := 'NULLABLE';
      END IF;
      IF (column_rec.data_precision IS NULL) THEN
        DBMS_OUTPUT.put_line('............... ' ||
                             RPAD(index_column_rec.column_name, 32) ||
                             RPAD(column_rec.data_type, 11) ||
                             '(' || TO_CHAR(column_rec.data_length) || ')'
||
                             '   ' || null_col);
      ELSE  -- a number
        DBMS_OUTPUT.put_line('............... ' ||
                             RPAD(index_column_rec.column_name, 32) ||
                             RPAD(column_rec.data_type, 11) ||
                             '(' || TO_CHAR(column_rec.data_precision) ||
',' ||
                             TO_CHAR(column_rec.data_scale) || ')' ||
                             '   ' || null_col);
      END IF;  -- display data size properly for type

    END LOOP; -- list of columns for this index     CLOSE index_column_cur;
  END LOOP; -- main_index
  CLOSE main_index_cur;

  IF (index_cnt = 0) THEN -- see if any indexes were found     DBMS_OUTPUT.put_line('>> NO INDEXES DEFINED');   END IF; EXCEPTION
WHEN OTHERS THEN
  BEGIN
    status := SQLCODE; -- trap error
    IF (main_index_cur%ISOPEN) THEN -- close any open cursors       CLOSE main_index_cur;
    END IF;
    IF (index_column_cur%ISOPEN) THEN
      CLOSE index_column_cur;
    END IF;
    IF (column_cur%ISOPEN) THEN
      CLOSE column_cur;
    END IF;
    DBMS_OUTPUT.put_line('SQL ERROR: ' || SQLERRM(status));   EXCEPTION
  WHEN OTHERS THEN
    NULL; -- avoid infinite loop
  END; END get_index; -- get index info
/
SHOW ERRORS grant execute on get_index to public;
DROP PUBLIC SYNONYM GET_INDEX;
create public synonym get_index for SYS.get_index;

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
Jonathan Gennick wrote in message
<3668edbc.12961613_at_netnews.worldnet.att.net>...

>On Fri, 27 Nov 1998 14:25:27 +0100, "Bernhard Mandl"
><b.mandl_at_ping.at> wrote:
>
>>1.) if a table has a primary key defined and
>>2.) if so, what's the index name of the primary key
>
>The index name and the primary key name usually match.
>However, the following query should give you a list of
>indexes for a table:
>
> select index_name from user_indexes
> where table_name = 'XXXXXX';
>
>You can get at the index columns by looking at the
>user_ind_columns view, or is it user_index_cols? I always
>get that mixed up.
>
>regards,
>
>Jonathan

Received on Sun Nov 29 1998 - 10:27:24 CST