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