--DESCRIPTION
--Describe all the indexes that are defined on
--the specified table.
--
--INPUTS
--  param 1   The table name, optionally qualified by
--            an owner name using the standard dot notation.
--
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
CLEAR COLUMNS

--Dissect the input argument, and get the owner name and
--table name into two separate substitution variables.
--The owner name defaults to the current user.l
DEFINE s_owner_name = ' '
DEFINE s_table_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_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 table name was passed in.*/
	 UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name
  FROM dual;

--
--Create a file of commands that will use the show_index.sql script
--to describe each index defined on the table.
SET PAGESIZE 0
SET HEADING OFF
SPOOL list_indexes_1.sql

--Generate the show index commands.
SELECT '@show_index ' || owner || '.' || index_name
  FROM all_indexes
 WHERE table_owner = '&&s_owner_name'
   AND table_name = '&&s_table_name';

--Generate an error message if the table does not exist.
SELECT 'PROMPT Table &&s_owner_name..&&s_table_name does not exist.'
  FROM DUAL
 WHERE NOT EXISTS (
                    SELECT *
		      FROM all_tables
		     WHERE owner = '&&s_owner_name'
		       AND table_name = '&&s_table_name'
		  );

--Generate a message if no indexes at all exist for the table.
SELECT 'PROMPT Table &&s_owner_name..&&s_table_name has no indexes defined.'
  FROM all_tables
 WHERE owner = '&&s_owner_name'
   AND table_name = '&&s_table_name'
   AND NOT EXISTS (
                    SELECT *
		      FROM all_indexes
		     WHERE table_owner = '&&s_owner_name'
		       AND table_name = '&&s_table_name'
		  );

SPOOL OFF

--Reset everything back to its default.
SET PAGESIZE 14
SET TERMOUT ON
SET FEEDBACK ON
SET VERIFY ON
CLEAR COLUMNS

--Tell the user what she is goung to see.
PROMPT
PROMPT INDEXES DEFINED ON TABLE &&s_owner_name..&&s_table_name

--Execute the script that was just created to
--display all the indexes on the table.
@list_indexes_1
