Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to get the index name of the primary key ?

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

From: Daniel Clamage <dclamageNOSPAM_at_telerama.com>
Date: Mon, 30 Nov 1998 23:00:10 -0000
Message-ID: <36636b41.0@paperboy.telerama.com>


Here is my improved get_index program to show all indexes (and column information) on any table in any schema, and indicate whether 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 (PRIMARY KEY CONSTRAINT)
   ............... 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.
   V.003 30-NOV-1998 - djc - Check if index created by constraint.
*/

  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
    IF (main_index_rec.uniqueness = 'UNIQUE') THEN -- check if a constraint

      OPEN is_constraint_cur(main_index_rec.owner,
                             main_index_rec.index_name,
                             utable_name);
      FETCH is_constraint_cur INTO is_constraint_rec;
      CLOSE is_constraint_cur;
    ELSE  -- clear old value
      is_constraint_rec.ctype := NULL;

    END IF;
    DBMS_OUTPUT.put_line('........ INDEX: ' || main_index_rec.index_name ||
                         '  ' || main_index_rec.uniqueness ||
                         is_constraint_rec.ctype);
    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;
    IF (is_constraint_cur%ISOPEN) THEN
      CLOSE is_constraint_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. Received on Mon Nov 30 1998 - 17:00:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US