REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_tab_const.sql
REM
REM <SQLDIR_GRP>TAB</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports all constraints for a given table or owner
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tab_const.sql
REM 
REM Example:
REM	s_tab_const.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

ttitle off
clear breaks
clear columns
clear computes

set term on
set feedback on
set echo off
set pages 90
set lines 145
set verify off

break on owner on table_name skip 1
-- compute count of constraint_name on owner

col owner format a6 heading "Owner"
col constraint_name format a24 heading "Constraint|Name"
col type format a4 heading "Type"
col table_name format a20 heading "Table Name"
col r_owner format a6 heading "Ref|Owner"
col r_constraint_name format a24 heading "Ref|Constraint|Name"
col delete_rule format a9 heading "Delete|Rule"
col status format a6 heading "Status"
col search_condition format a35 heading "Search Condition"
set long 100000

accept owner prompt "Enter Constraint Owner Name [Enter For All]:"
accept type prompt "Do You Want to List NOT NULL Constraints [NO]:"
accept table_name prompt "Enter Table Name [Enter For All]:"


select		owner
,		table_name
,		constraint_name
,		decode(constraint_type,
			'P','- PK',
			'R','- FK',
			'C','- NN',
			'U','- UN')			type
,		decode(status,
			'ENABLED','- On',
			'DESABLED','- Off')		status
,		r_owner
,		r_constraint_name
,		delete_rule
,		search_condition
from		dba_constraints
where		owner = nvl(upper('&owner'),owner)
and		constraint_type != decode(nvl(upper( '&type'),'NO'),'NO','C',0 )
and		table_name = nvl(upper('&table_name'),table_name)
order by	table_name
,		owner
,		constraint_name
/