-- Report indexes on table
-- Usage: @ind tablename
-- William Robertson, www.williamrobertson.net

def table = &1

COL index_name                        HEA "Index"
COL uniqueness    FORMAT A7           HEA "Unique?"
COL index_type    FORMAT A17          HEA "Type"
COL status        FORMAT A8           HEA "Status"
COL num_rows      FORMAT 999,999,990  HEA "Rows"
COL distinct_keys FORMAT 999,999,990  HEA "Distinct|keys"
COL column_name   FORMAT A25          HEA "Column name"

cl bre
break on index_name on uniqueness on index_type on status on num_rows on distinct_keys skip1

set term off
store set sqlplus_settings.sql replace
set feed off serverout on term on autoprint off
var object_name VARCHAR2(61)
var output REFCURSOR

exec :object_name := UPPER('&table')

DECLARE
	k_object_name CONSTANT VARCHAR2(30) := :object_name;
	v_object_type all_objects.object_type%TYPE;
	v_table_name  all_objects.object_name%TYPE := SUBSTR(k_object_name,INSTR(k_object_name,'.') +1);
	k_table_owner CONSTANT all_objects.owner%TYPE :=
		NVL(SUBSTR(k_object_name,1,INSTR(k_object_name,'.') -1), SYS_CONTEXT('USERENV','CURRENT_SCHEMA'));
BEGIN
	SELECT object_type INTO v_object_type
	FROM   all_objects
	WHERE  owner = k_table_owner
	AND    object_name = v_table_name
	AND    object_type IN ('TABLE','INDEX');

	IF v_object_type = 'INDEX' THEN
		SELECT table_name
		INTO   v_table_name
		FROM   all_indexes
		WHERE  index_name = k_object_name
		AND    table_owner = k_table_owner;

		DBMS_OUTPUT.PUT(CHR(10)||'Table ');

		IF k_table_owner <> SYS_CONTEXT('USERENV','CURRENT_SCHEMA') THEN
			DBMS_OUTPUT.PUT(k_table_owner || '.');
		END IF;

		DBMS_OUTPUT.PUT_LINE(v_table_name);
	END IF;

	OPEN :output FOR
		SELECT i.index_name
		     , DECODE(i.uniqueness,
		             'UNIQUE', 'Y', 'N') AS uniqueness
		     , i.index_type
		     , i.status
		     , DECODE(SIGN(i.num_rows),
		              -1, TO_NUMBER(NULL),  -- Have seen this in 8i
		               i.num_rows ) AS num_rows
		     , i.distinct_keys
		     , c.column_name
		FROM   all_indexes i
		     , all_ind_columns c
		WHERE  i.table_name = v_table_name
		AND    i.owner = k_table_owner
		AND    (    ( v_object_type = 'INDEX' AND i.index_name = k_object_name )
		        OR  v_object_type = 'TABLE' )
		AND    c.index_name = i.index_name
		AND    c.table_name = i.table_name
		AND    c.table_owner = i.table_owner
		ORDER BY i.index_name, c.column_position;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		OPEN :output FOR
			SELECT 'No such table or index "' || k_object_name || '"' AS error
			FROM dual;
END;
/

set term on
print :output
cl bre
@sqlplus_settings.sql

set term on