Title: Displaying User Constraints and Indexes Reports

Author:Shanmukha Immadi, an Oracle DBA for Complete Business Solutions, Inc. in
Farmington Hills, Michigan USA.

As a DBA, I take care of database activities for different applications. During database migration or application tuning, I need
to check the constraints and indexes. Since I am working on different applications, I put the whole thing in a generic script,
which is very handy for anyone who is working on different systems. This simple tool reports constraints and indexes details in
each table in a report format.

The argument for this report is "Owner Name" of the objects in the database. It prompts for "Owner Name." and generates a
detailed report of all constraints and Indexes of all tables under the owner name.

-- Prompt for Argument
prompt
prompt Please Enter Valid Owner Name
prompt
accept 1 char prompt 'Owner Name : '
prompt
prompt
prompt **** Please Wait! It takes a few minutes ****
prompt (Output File : table_n_cons_index_info.log)
prompt

set termout off
set echo off
---------------------------------------------------------------------
--  Name        : table_n_cons_index_info.sql

--  Description : This SQL Script generates all User Constraints and
                Index information

--  Argument(s) : Owner Name

--  Note        : Output generated at specified location of the spooler

--  Author      : Shanmukha Immadi, CBSI
---------------------------------------------------------------------
set embedded off
set feedback off
set verify   off
set linesize 110
set newpage  0
set pagesize 55
set pause    off

set newpage  0
clear columns
clear breaks

col today noprint  new_value              datevar
col col1  heading 'Table Name'            format a30
col col2  heading 'Constraint/Index Name' format a30
col col3  heading 'Column Name'           format a30
col col4  heading 'Position'              format 99999999

break on col1 skip 1 on col2 skip 1

spool table_n_cons_index_info.log

ttitle left    datevar -
       center 'Table Vs Constraints and Indices List of user (&1)' -
       right  'Page:' format 999 sql.pno skip 2

btitle center '-- End of Page --' skip 1

select   to_char(sysdate, 'MON DD, YYYY') today,
         table_name col1,
         constraint_name col2,
         column_name col3,
         position  col4
from     all_cons_columns
where    owner = upper('&1')
union
select   to_char(sysdate, 'MON DD, YYYY') today,
         table_name col1,
         index_name col2,
         column_name col3,
         column_position  col4
from     all_ind_columns
where    table_owner = upper('&1')
and      index_name not in (select constraint_name
                            from   all_cons_columns)
order by 2, 3, 5
/

exit