Kbase 15517: What index/indexes does this Oracle table have?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/05/1998 |
|
What index/indexes does this Oracle table have?
This SQL program can be use to find out if an Oracle table has any
indexes defined.
SELECT i.table_name,
i.index_name,
i.uniqueness,
i.status,
ic.column_name
FROM user_indexes i, user_ind_columns ic
WHERE i.table_name = ic.table_name
AND i.index_name = ic.index_name
AND i.table_name = 'STATE' ------------> **
ORDER BY i.uniqueness,
i.index_name,
ic.column_position;
** Remove this line if you want to query all the tables OR
Replace STATE with the table name of your choice if you are only
insterested in a particular table.
In order for this SQL program to work; you must have already run
$ORACLE_HOME/rdbms/admin/catalog.sql while connected as internal in
Oracle version 7.2 and below. $ORACLE_HOME/rdbms/admin/catalog.sql is
an Oracle supplied program that creates the data dictionary and public
synonyms for many of its views, and grants PUBLIC access to the
synonyms; also calls scripts cataudit.sql, catexp.sql and catldr.sql
(refer to Oracle7 Server Administrator's guide for details of these
scripts).
Progress Software Technical Support Note # 15517