Re: *** Table Constraints ***

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 26 Aug 1999 20:43:52 GMT
Message-ID: <19990826164352.01796.00002462@ngol08.aol.com>

/*

Reporting Table Constraints
*/

/*

This script reports most of the constraints against a given table. If the constraint is a primary key, it will show all columns involved and all tables that reference it through foreign key constraints. If the constraint is a foreign key, it will show all columns involved and the table it references.
Otherwise it will describe the rule of the constraint. */

REM Script:
REM tbconst.sql
REM REM

REM             NOTE * Need select access against sys.dba_cons_columns and
REM                    sys.dba_constraints to run this.

REM
REM Parameter:

REM             owner = owner of the table
REM             table = name of table to report on

REM
REM Usage:
REM SQL> @tbconst.sql
REM
REM Oracle Version:
REM Tested on Version 7.1.4 as a dba user REM Tested on Version 7.2.3 as user sys REM
REM History:
REM
REM DATE(DMY) AUTHOR DESCRIPTION

REM ---------- ----------------- ----------------------------------------

REM Capture owner and table name parameters def owner = &owner
def table_name = &table_name
REM Define working variables
def gScript = 'tbconst.sql'
def gTitle = 'Constraints against table &owner..&table_name' REM Set the system variables
set concat on
set echo off
set embedded off
set pagesize 58
set showmode off
set space 1
set termout on
set trimout on
set verify off
set wrap on
REM Get today's date
set termout off
col today new_value now noprint
select to_char(sysdate, 'DD Mon YYYY HH:MIam') today from dual; REM Get the name of the database
col dbname new_value sid noprint
select name dbname from v$database;
REM Set the report title based on the information gathered and passed clear breaks
set termout on
set heading on
ttitle - left 'Database: &sid' right now skip 0 - left ' Report: &gScript' right 'Page ' sql.pno skip 2 - center '&gTitle' skip 2 set newpage 0
REM Run the Report
set linesize 80
set arraysize 1
create or replace package pdcconstraint as function pdc$getcols ( cn_name in varchar2, cn_owner in varchar2, tb_name in varchar2)
return varchar2;
 pragma restrict_references (pdc$getcols, WNDS,WNPS);  function pdc$getdesc ( cn_name in varchar2, cn_owner in varchar2, tb_name in varchar2)
 return varchar2;
 pragma restrict_references (pdc$getdesc, WNDS,WNPS);  end pdcconstraint;
/

 create or replace package body pdcconstraint as  function pdc$getcols ( cn_name in varchar2, cn_owner in varchar2, tb_name in varchar2)
 return varchar2
 as
 val varchar2(500);
 col_name varchar2(30);
 found boolean;
 cursor c1 is
 select column_name
 from sys.dba_cons_columns
 where constraint_name = upper(cn_name)  and owner = upper(cn_owner)
 and table_name = upper(tb_name);
 Begin

    val := '';
    found := FALSE;
    for record in c1
    Loop

        if found = FALSE then
        found := TRUE;
        else val := val ||', ';
        end if;
        val := val || record.column_name;

    end loop;
    return val;
 end pdc$getcols;

 function pdc$getdesc ( cn_name in varchar2, cn_owner in varchar2, tb_name in varchar2) return varchar2
 as cn_type char(1);
 descr varchar2(2000);
 found boolean;

 cursor c1 is
 select owner || '.' || table_name val
 from sys.dba_constraints
 where r_owner = upper(cn_owner)
 and r_constraint_name = upper(cn_name);

 begin

     found := FALSE;
     descr := '';
     select constraint_type
     into cn_type
     from sys.dba_constraints
     where constraint_name = upper(cn_name)
     and owner = upper(cn_owner)
     and table_name = upper(tb_name);
     if cn_type = 'U' then
      descr := ' ';
     else if cn_type = 'P' then
      descr := 'Referenced by: ';
     for record in c1 loop
        if found = FALSE then
            found := TRUE;
        else descr := descr || ', ';
        end if;
        descr := descr || record.val;
     end loop;
     else if cn_type = 'R' then
        select 'References ' || b.owner || '.' || b.table_name
        into descr
        from dba_constraints a, dba_constraints b
        where a.table_name = upper(tb_name)
        and a.owner = upper(cn_owner)
        and a.constraint_name = upper(cn_name)
        and b.owner = a.r_owner
        and b.constraint_name = a.r_constraint_name;
        else if cn_type = 'C' then
                select search_condition
                into descr
                from dba_constraints
                where constraint_name = upper(cn_name)
                and owner = upper(cn_owner)
                and table_name = upper(tb_name);

                descr := ltrim(descr);
            else descr := ' ';
            end if;
            end if;
            end if;
            end if;
            return descr;

end pdc$getdesc;
end pdcconstraint;
/

rem
col constraint_name format a12 heading 'Constraint|Name' col type format a11 heading 'Type'
col cols format a21 heading 'Columns'
col des format a33 heading 'Description'

select constraint_name,

        'Primary Key' type,
        pdcconstraint.pdc$getcols(constraint_name,'&owner','&table_name')
        cols, pdcconstraint.pdc$getdesc(constraint_name,'&owner','&table_name')

des
from dba_constraints
where owner=upper('&owner')
and table_name = upper('&table_name')
and constraint_type = 'P'
union

    select constraint_name,

            'Referential' type,
            pdcconstraint.pdc$getcols(constraint_name,'&owner','&table_name')
cols,
            pdcconstraint.pdc$getdesc(constraint_name,'&owner','&table_name')

des

    from dba_constraints
    where owner=upper('&owner')
    and table_name =upper('&table_name')     and constraint_type = 'R'
union

    select constraint_name,

            'Table Check' type,
            pdcconstraint.pdc$getcols(constraint_name,'&owner','&table_name')
cols,
            pdcconstraint.pdc$getdesc(constraint_name,'&owner','&table_name')

des

    from dba_constraints
    where owner=upper('&owner')
    and table_name = upper('&table_name')     and constraint_type = 'C'
union

    select constraint_name,

            'View Check' type,
            pdcconstraint.pdc$getcols(constraint_name,'&owner','&table_name')
cols,
            pdcconstraint.pdc$getdesc(constraint_name,'&owner','&table_name')

des

    from dba_constraints
    where owner=upper('&owner')
    and table_name = upper('&table_name')     and constraint_type = 'V'
union

    select constraint_name, 'Unique' type,
pdcconstraint.pdc$getcols(constraint_name,'&owner','&table_name') cols,
pdcconstraint.pdc$getdesc(constraint_name,'&owner','&table_name') des

    from dba_constraints
    where owner=upper('&owner')
    and table_name = upper('&table_name')     and constraint_type = 'U'
order by 2,1
/

drop package pdcconstraint;
REM Clear variables
undefine owner
undefine table_name
undefine gScript
undefine gTitle
ttitle off
btitle off
clear column
clear breaks
REM End of Script

Paul in VT Received on Thu Aug 26 1999 - 15:43:52 CDT