Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to get the index name of the primary key ?
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 :
............... DEPTNO NUMBER (2,0) NOT NULLModification:
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;
' ' || 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 typeEND LOOP; -- list of columns for this index CLOSE index_column_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