-- constr.sql
-- List constraints for a table owned by the current user
--
-- Uses types:
-- *  DB_CONSTRAINT_OT modelled on ALL_CONSTRAINTS record
-- *  DB_CONSTRAINT_TT collection of DB_CONSTRAINT_OT
-- *  VARCHAR2_TT generic VARCHAR2 collection. 
-- Uses bind variables when querying data dictionary.
-- Returns results to SQL*Plus as ref cursor.
--
-- William Robertson 2003, www.williamrobertson.net

-- CREATE OR REPLACE TYPE db_constraint_ot AS OBJECT
-- ( owner                           VARCHAR2(30)
-- , constraint_name                 VARCHAR2(30)
-- , constraint_type                 VARCHAR2(1)
-- , table_name                      VARCHAR2(30)
-- , search_condition                VARCHAR2(32767)
-- , r_owner                         VARCHAR2(30)
-- , r_constraint_name               VARCHAR2(30)
-- , delete_rule                     VARCHAR2(9)
-- , status                          VARCHAR2(8)
-- , deferrable                      VARCHAR2(14)
-- , deferred                        VARCHAR2(9)
-- , validated                       VARCHAR2(13)
-- , generated                       VARCHAR2(14)
-- , bad                             VARCHAR2(3)
-- , rely                            VARCHAR2(4)
-- , last_change                     DATE
-- , index_owner                     VARCHAR2(30)
-- , index_name                      VARCHAR2(30)
-- , invalid                         VARCHAR2(7)
-- , view_related                    VARCHAR2(14)       )
-- /
-- 
-- CREATE OR REPLACE TYPE db_constraint_tt AS TABLE OF db_constraint_ot
-- /
--
-- CREATE OR REPLACE TYPE varchar2_tt AS TABLE OF VARCHAR2(4000)
-- /

def table = &1
set term off
store set sqlplus_settings replace
TTITLE OFF
var tabname VARCHAR2(30)
var results REFCURSOR
exec :tabname := UPPER('&table')
col table new_value table
SELECT UPPER(:tabname) AS "TABLE" FROM dual;
set def on term on autoprint off feed off lines 110

col type hea "Type"
col constraint_name hea "Constraint name"
col table_name hea "Table name"
col table_owner format a20 hea "Owner"
col status hea "Status"
col deferrable hea "Deferrable?"
col deferred hea "Deferred?"

col search_condition format a60 word hea "Definition"

DECLARE
	v_table_exists VARCHAR2(1) := 'Y';
	v_constraints DB_CONSTRAINT_TT := DB_CONSTRAINT_TT();

	CURSOR c_constraints (cp_tablename all_constraints.table_name%TYPE)
	IS
		SELECT c.owner
		     , CASE
		           WHEN c.generated = 'GENERATED NAME' AND c.constraint_name LIKE 'SYS\_%' ESCAPE '_'
		                THEN '[' || c.constraint_name || ']'
		           ELSE c.constraint_name 
		       END AS constraint_name
		     , c.constraint_type
		     , c.table_name
		     , c.search_condition
		     , TO_CHAR(NULL) AS column_list
		     , DECODE(c.constraint_type,
		              'R', 'Foreign key (%COLS%) to ' || r.table_name
		                   || ' (' || c.r_constraint_name
		                   || ')'
		                   || DECODE(c.delete_rule, 'CASCADE', ', ' || c.delete_rule),
		              'P', 'Primary key (%COLS%)',
		              'U', 'Unique key (%COLS%)'
		             ) key_description
		     , CAST
		       ( MULTISET
		         ( SELECT column_name
		           FROM   all_cons_columns
		           WHERE  owner = c.owner
		           AND    constraint_name = c.constraint_name
		           ORDER BY position
		         ) AS VARCHAR2_TT ) key_columns
		     , c.r_owner
		     , c.r_constraint_name
		     , c.delete_rule
		     , c.status
		     , c.deferrable
		     , c.deferred
		     , c.validated
		     , c.generated
		     , c.bad
		     , c.rely
		     , c.last_change
		     , c.index_owner
		     , c.index_name
		     , c.invalid
		     , c.view_related
		FROM   all_constraints c
		     , all_constraints r
		WHERE  c.table_name = cp_tablename
		AND    c.owner = USER
		AND    r.constraint_name (+)= c.r_constraint_name
		AND    r.owner (+)= c.r_owner;
BEGIN
	FOR r IN c_constraints(:tabname)
	LOOP
		v_constraints.EXTEND;

		IF r.key_columns.COUNT > 0 THEN
			FOR i IN r.key_columns.FIRST..r.key_columns.LAST LOOP
				r.column_list := r.column_list || r.key_columns(i) || ', ';
			END LOOP;
			r.column_list := RTRIM(r.column_list,', ');
		END IF;

		r.key_description := REPLACE(r.key_description,'%COLS%', r.column_list);

		v_constraints(c_constraints%ROWCOUNT) :=
		DB_CONSTRAINT_OT
		( r.owner
		, r.constraint_name
		, r.constraint_type
		, r.table_name
		, NVL(r.search_condition,r.key_description)
		, r.r_owner
		, r.r_constraint_name
		, r.delete_rule
		, r.status
		, r.deferrable
		, r.deferred
		, r.validated
		, r.generated
		, r.bad
		, r.rely
		, r.last_change
		, r.index_owner
		, r.index_name
		, r.invalid
		, r.view_related );
	END LOOP;

	IF v_constraints.COUNT = 0 THEN
		-- Nothing in ALL_CONSTRAINTS - check table exists:
		SELECT MIN('N') INTO v_table_exists
		FROM   dual
		WHERE  NOT EXISTS
		       ( SELECT 1 FROM all_tables
		         WHERE  table_name = :tabname
		         AND    owner = USER );

		IF v_table_exists = 'N' THEN
			DBMS_OUTPUT.PUT_LINE('No such table "' || :tabname || '"');
		END IF;
	END IF;

	OPEN :results FOR
	SELECT CASE
	       WHEN search_condition LIKE '"%" IS NOT NULL' THEN 'Not Null'
	       WHEN constraint_type = 'C' THEN 'Check'
	       WHEN constraint_type = 'U' THEN 'Unique'
	       WHEN constraint_type = 'P' THEN 'Primary'
	       WHEN constraint_type = 'R' THEN 'FK'
	       ELSE constraint_type
	       END AS type
	     , constraint_name
	     , search_condition
	     , status
	FROM   TABLE(v_constraints)
	ORDER BY 
	       CASE
	       WHEN constraint_type = 'P' THEN '1'
	       WHEN constraint_type = 'U' THEN '2'
	       WHEN constraint_type = 'R' THEN '3'
	       WHEN constraint_type LIKE '"%" IS NOT NULL' THEN '4'
	       WHEN constraint_type = 'C' THEN '5'
	       ELSE constraint_type
	       END
	     , constraint_name;
END;
/

set term on

print :results

TTITLE ON LEFT 'Tables referencing &table:' SKIP1

SELECT -- r_owner AS table_owner
       table_name
     , constraint_name
     , status
     , deferrable
     , deferred
FROM   all_constraints
WHERE  constraint_type = 'R'
AND    r_owner = USER
AND    r_constraint_name IN
       ( SELECT constraint_name
         FROM   user_constraints
         WHERE  table_name = :tabname
         AND    constraint_type IN ('P','U') );

prompt
TTITLE ""
TTITLE OFF
@sqlplus_settings.sql