--DESCRIPTION
--Displays information about an index. The index name
--is passed as a parameter to this script.
--
--INPUTS
--  param1    Name of the index to be described.
--
SET ECHO OFF
SET RECSEP OFF
SET NEWPAGE NONE
SET VERIFY OFF
SET PAGESIZE 9999
SET HEADING OFF
SET LINESIZE 80
SET FEEDBACK OFF
CLEAR COMPUTES
CLEAR COLUMNS
CLEAR BREAKS

--Turn off terminal output to avoid spurious blank lines 
--caused by the SELECTS that are done only to load the
--substitution variables
SET TERMOUT OFF

--Dissect the input argument, and get the owner name and
--index name into two seperate substitution variables.
--The owner name defaults to the current user.
DEFINE s_owner_name = ' '
DEFINE s_index_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN index_name NOPRINT NEW_VALUE s_index_name
SELECT
  DECODE(INSTR('&&1','.'),
         0,USER,  /*Default to current user.*/
	 UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
  DECODE(INSTR('&&1','.'),
         0,UPPER('&&1'),  /*Only the index name was passed in.*/
	 UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) index_name
  FROM dual; 

--Retrieve information about the index as a whole.
DEFINE s_table_owner
DEFINE s_table_name
DEFINE s_index_type
DEFINE s_uniqueness
DEFINE s_tablespace_name
COLUMN table_owner NOPRINT NEW_VALUE s_table_owner
COLUMN table_name NOPRINT NEW_VALUE s_table_name
COLUMN index_type NOPRINT NEW_VALUE s_index_type
COLUMN uniqueness NOPRINT NEW_VALUE s_uniqueness
COLUMN tablespace_name NOPRINT NEW_VALUE s_tablespace_name
SELECT table_owner, table_name, index_type, uniqueness, tablespace_name
  FROM all_indexes
 WHERE owner = '&&s_owner_name'
   AND index_name = '&&s_index_name';

--Information about the index as a whole is printed in
--the page title.
TTITLE SKIP 1 LEFT 'INDEX ' &&s_owner_name..&&s_index_name -
       ' ' &&s_index_type ' ' &&s_uniqueness SKIP 1 -
       'DEFINED ON TABLE ' &&s_table_owner..&s_table_name SKIP 1 -
       'STORED IN TABLESPACE ' &&s_tablespace_name SKIP 1 -
       'CONTAINING COLUMNS: '
--List the columns that make up the index.
--The indent column moves the column list over to the
--right so that it comes after the 'CONTAINING COLUMNS:'
--portion of the header.
SET TERMOUT ON
COLUMN indent FORMAT A19
COLUMN column_name FORMAT A30
SELECT ' ' indent,
       column_name
  FROM all_ind_columns
 WHERE index_owner = '&&s_owner_name'
   AND index_name = '&&s_index_name'
ORDER BY column_position;


--Spit out an error message if the index does not exist.
TTITLE SKIP 1
SELECT 'Index &&s_owner_name..&&s_index_name does not exist.'
  FROM DUAL
 WHERE NOT EXISTS (
                    SELECT *
		      FROM all_indexes
		     WHERE owner = '&&s_owner_name'
		       AND index_name = '&&s_index_name'
		  );

--Change all settings back to defaults
CLEAR COLUMNS
CLEAR BREAKS
UNDEFINE s_owner_name
UNDEFINE s_index_name
UNDEFINE s_index_type
UNDEFINE s_uniqueness
UNDEFINE s_tablespace_name
SET PAGESIZE 14
SET HEADING ON
SET NEWPAGE 1
SET FEEDBACK ON
