set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 220
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT
PROMPT ###################################################################### 
PROMPT 
PROMPT --  Name          : listConstraintsInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   : This SQL Plus script lists constraints info for tables. It can list 
PROMPT                     constraints in all schemas or you can specify the schema
PROMPT 
PROMPT --  Input(s)     : None
PROMPT 
PROMPT --  Output       : The output is in a file named constraintsInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT ######################################################################
PROMPT 

column  constraint_name  format a20    heading "Constraint|Name" word_wrap
column  r_constraint_name format a20   heading "Referenced|Constraint|Name" word_wrap
column  constraint_type  format a18    heading "Constraint|Type" 
column  owner            format a10    heading "Owner" wrap
column  r_owner          format a10    heading "Referenced|Owner"  wrap
column  column_name      format a30    heading "Column|Name"       word_wrap
column  column_position  format 999    heading "Column|Position"
column  search_condition format a25    heading "Search Condition"  word_wrap
column  last_change      format a9     heading "Last|Change"
column  delete_rule      format a9     heading "Delete|Rule"
column  table_name                     heading "Table Name"
column  status                         heading "Status"
column  validated                      heading "Validated"
column  referenced_table               heading "Referenced|Table Name"
column  referenced_column format a30   heading "Referenced|Column"

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on

PROMPT The database has the following schema owners : 
PROMPT
-- Get list of segment owners
select distinct(owner) "schema owner" from dba_segments
 where owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'OUTLN', 'DBSNMP')
 order by owner;

-- allow user name to be entered.  NULL/return key is for all locks/users
accept u_name prompt 'Enter the name of schema owner to show constraints for (return for all owners) : ' 

--set termout off
set feedback off

set head on

spool constraintsInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "List of constraints for Tables By Owner for "  &s_dbname " on " &s_machine skip 2

-- Get Constraints information
break on owner nodup skip page on constraint_type nodup skip 1
select owner, table_name, constraint_name, 
       decode(constraint_type, 'C', 'CHECK', 'P', 'PRIMARY', 'U', 'UNIQUE', 'R', 'REFERENTIAL', constraint_type) constraint_type,
       search_condition, status, validated
from dba_constraints
where owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'OUTLN', 'DBSNMP')
and nvl(owner,1) like upper(nvl('&&u_name','%'))
ORDER by owner, constraint_type, table_name;

ttitle left &s_curDate -
       center "List of referential integrity constraints for Tables By Owner for "  &s_dbname " on " &s_machine skip 2

-- Get Referential Integrity Constraints Information
break on owner nodup skip page on constraint_type nodup skip 1
select owner, table_name, constraint_name, r_owner, r_constraint_name, delete_rule, status, validated
from dba_constraints
where owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'OUTLN', 'DBSNMP')
and nvl(owner,1) like upper(nvl('&&u_name','%'))
and constraint_type ='R'
ORDER by owner, r_owner, table_name;

spool off
ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty, no constraint information was found for the database &s_dbname
PROMPT --                or no constraint information was found for the username &u_name
PROMPT
PROMPT Output file constraintsInfo_&s_dbname._&s_machine..LST produced
PROMPT

undefine u_name


@sqlplus_settings