This script gives you index information for a given table. It accepts the name of the table as an argument. Back to SQL scripts

SET verify OFF
SET lines 120

UNDEFINE table
ACCEPT Table CHAR Prompt "Please enter your table name: "

COLUMN Asc/Desc FORMAT A8
COLUMN "Column Name" Format A30
TTITLE 'Indexes for table &&table'

SELECT ind.Index_name "Name", ind.index_type "Type", ind.uniqueness "Unique?",
col.column_name "Column Name", col.column_position "Position", col.descend "Asc/Desc"
FROM all_indexes ind, all_ind_columns col
WHERE ind.table_name = col.table_name
AND ind.table_owner = col.table_owner
AND ind.index_name = col.index_name
AND ind.table_name = '&&table'
/

SET verify ON
SET lines 80

To run the code, cut and paste it onto your favourite editor and save the file to say index.sql. At the SQL*PLUS prompt, execute the script using the syntax 'SQL>@index'. The script prompts you for the name of the table. It then displays information w.r.t the indexes created for the table.

For the emp table, for example, you get the following output.

SQL>@index
Please enter your table name: EMP

Wed May 09 page 1
Indexes for table EMP
Name Type Unique? Column Name Position Asc/Desc
-----------------------------------------------------------------------------------------------------------------
PK_EMPNORMALUNIQUEEMPNO1ASC

SQL>

Back to SQL scripts
Hosted by www.Geocities.ws

1