Constr
-- 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
left join all_constraints r
on r.constraint_name = c.r_constraint_name
and r.owner = c.r_owner
where c.table_name = cp_tablename
and c.owner = user
;
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