-- 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