-- #############################################################################################
--
-- %Purpose: Show Constraints of Tabelles for a Schema Owner which be choosen
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
spool show_constraints.lst
ttitle off;
column owner format a8 heading 'Table|Owner'
column constraint_name format a30 heading 'Constraint|Name'
column search_condition format a30 heading 'Text'
column table_name noprint new_value tab
column column_name format a15 heading 'Column|Name'
column r_constraint_name format a30 heading 'Reference|Constraint'
column status format A8 heading 'Status'

ttitle left 'Constraints of Tabelle: 'tab skip 2

ACCEPT user_namen CHAR PROMPT 'Schema Owner: '

break on table_name skip page -
on owner skip 2 -
on constraint_name skip -

select c.constraint_name,
     i.search_condition,
     c.table_name,
     c.column_name,
     i.r_constraint_name,
     i.status
from dba_cons_columns c,
     dba_constraints i
where i.table_name = c.table_name
  and i.constraint_name = c.constraint_name
  and i.owner = c.owner
  and i.owner LIKE UPPER('&user_namen')
order by c.table_name,
       c.constraint_name,
         c.column_name;
spool off;