Integrity referential constraint path on a table

This is a PL/SQL procedure that allows to display the integrity referential path for a table.

The result on a single table looks like the following :

-----------------------------------------------------------------
-- Integrity referential constraints on table : LOV_LOV.GENLOV --
-----------------------------------------------------------------
LOV_LOV                         referenced by : 1 -> Item_Id.LOV_ITEM
___ LOV_ITEM                        referenced by : 1 -> Module.LOV_BLOCK
___ LOV_ITEM                        referenced by : 2 -> Bloc.LOV_BLOCK
______ LOV_BLOCK                       referenced by : 1 -> Mod_Nom_Module.LOV_MODULE
-----------------------------------------------------------------
LOV_LOV                         references  : 1 -> Id.LOV_COLONNE  (CASCADE)
LOV_LOV                         references  : 1 -> Id.LOV_ELEMENT_COLONNE  (CASCADE)

This is the procedure:

CREATE OR REPLACE PROCEDURE P_TRACE_FK
  (
    PC$Table   IN VARCHAR2,
    PC$Schema  IN VARCHAR2 DEFAULT USER
  )
IS
  LN$Tab   PLS_INTEGER := 0 ;
  LC$Titre VARCHAR2(256) ;
  LN$L     PLS_INTEGER ;
  PROCEDURE reference( PC$Nom_Table IN VARCHAR2, PC$Nom_User IN VARCHAR2, PN$L IN PLS_INTEGER )  ;
  PROCEDURE Est_reference_par( PC$Nom_Table IN VARCHAR2, PC$Nom_User IN VARCHAR2 ) IS
  Cursor Cur IS
  -- Cursor on constraints --
  SELECT A1.TABLE_NAME, C1.COLUMN_NAME, C1.POSITION,
         B1.CONSTRAINT_NAME, A1.OWNER, a1.delete_rule
  FROM   ALL_CONSTRAINTS A1, ALL_CONSTRAINTS B1, ALL_CONS_COLUMNS C1
  WHERE  A1.Constraint_Name = B1.R_Constraint_name
  AND    A1.Owner=B1.R_Owner
  AND    A1.CONSTRAINT_TYPE = 'P'
  AND    C1.Table_name=B1.table_name
  AND    C1.Constraint_Name = B1.Constraint_name
  AND    C1.owner = B1.owner
  AND    B1.table_name = PC$Nom_Table
  AND    B1.owner= PC$Nom_User
  ORDER BY A1.Table_name, C1.Position ;

 LC$NewTable VARCHAR2(30) ;
 LC$OldTable VARCHAR2(30) ;
 LN$Num PLS_INTEGER := 0 ;

 BEGIN
  For C IN Cur Loop
     LC$OldTable := C.TABLE_NAME ;
     LC$NewTable := LC$OldTable ;
     LN$Num := LN$Num + 1 ;
     DBMS_OUTPUT.PUT_LINE( LPAD( '_' ,LN$Tab * 3,'_') || ' ' || Rpad(PC$Nom_table,31) || ' ' || 'referenced by : '
       || C.POSITION || ' -> ' || INITCAP(C.COLUMN_NAME) || '.' || C.TABLE_NAME ) ;
     If C.TABLE_NAME <> NVL( LC$OldTable,'1' ) Then
        LN$Tab := LN$Tab + 1 ;
        Est_reference_par( C.TABLE_NAME, PC$Nom_User ) ; -- Recursive call
        LC$OldTable := C.TABLE_NAME ;
     End if ;
  End loop ;
  If LN$Num = 1 OR ( LC$OldTable = LC$NewTable ) Then
    LN$Tab := LN$Tab + 1 ;
    Est_reference_par( LC$OldTable, PC$Nom_User ) ;
  End if ;
 END ;

  PROCEDURE reference( PC$Nom_Table IN VARCHAR2, PC$Nom_User IN VARCHAR2, PN$L IN PLS_INTEGER ) IS
  Cursor Cur IS
  SELECT B1.TABLE_NAME, C1.COLUMN_NAME, C1.POSITION, B1.CONSTRAINT_NAME, B1.OWNER, B1.DELETE_RULE
  FROM   ALL_CONSTRAINTS B1, ALL_CONS_COLUMNS C1
  WHERE  B1.R_Constraint_Name = C1.Constraint_name
  AND    B1.R_Owner=C1.Owner
  AND    B1.CONSTRAINT_TYPE = 'R'
  AND    C1.Table_name= PC$Nom_Table
  AND    C1.owner = PC$Nom_User
  ORDER BY b1.owner, B1.Table_name, C1.Position;

  BEGIN
    For C IN Cur Loop
      If Cur%ROWCOUNT = 1 Then
        DBMS_OUTPUT.PUT_LINE( RPAD( '-', PN$L, '-' ) ) ;
      End if ;
      DBMS_OUTPUT.PUT_LINE( Rpad(PC$Nom_table,31) || ' ' || 'references  : '
        || C.POSITION || ' -> ' || INITCAP(C.COLUMN_NAME)
        || '.' || C.TABLE_NAME || '  (' || C.DELETE_RULE || ')' ) ;
    End loop ;
  END ;

-- Début de la procédure --
BEGIN
  LC$Titre := '-- Integrity referential constraints on table : ' || PC$Table || '.' || PC$Schema || ' --' ;
  LN$L := Length( LC$Titre ) ;
  DBMS_OUTPUT.PUT_LINE( LPAD( '-', LENGTH( LC$Titre ), '-' ) ) ;
  DBMS_OUTPUT.PUT_LINE( LC$Titre ) ;
  DBMS_OUTPUT.PUT_LINE( LPAD( '-', LENGTH( LC$Titre ), '-' ) ) ;
  Est_reference_par( PC$Table, PC$Schema ) ;
  Reference( PC$Table, PC$Schema, LN$L ) ;
  DBMS_OUTPUT.PUT_LINE( CHR(10) ) ;
END;
/

One thing that is interresting is to loop through all the tables of a particular schema for the purpose of technical documentation

SQL> set linesize 200
SQL> set serveroutput on
SQL>
SQL> Declare
  2    Cursor C_Tables Is
  3    Select table_name from user_tables
  4    order by table_name ;
  5  Begin
  6    dbms_output.enable( 1000000 ) ;
  7    For C in C_Tables Loop
  8       P_TRACE_FK( C.TABLE_NAME ) ;
  9    End loop ;
 10  End ;
 11  /
------------------------------------------------------------------
-- Integrity referential constraints on table : DEPT_LOV.GENLOV --
------------------------------------------------------------------

-----------------------------------------------------------------
-- Integrity referential constraints on table : EMP_LOV.GENLOV --
-----------------------------------------------------------------

-------------------------------------------------------------------
-- Integrity referential constraints on table : LOV_BLOCK.GENLOV --
-------------------------------------------------------------------
LOV_BLOCK                       referenced by : 1 -> Mod_Nom_Module.LOV_MODULE
-------------------------------------------------------------------
LOV_BLOCK                       references  : 1 -> Mod_Nom_Module.LOV_ITEM  (CASCADE)
LOV_BLOCK                       references  : 2 -> Nom_Block.LOV_ITEM  (CASCADE)

---------------------------------------------------------------------
-- Integrity referential constraints on table : LOV_COLONNE.GENLOV --
---------------------------------------------------------------------
LOV_COLONNE                     referenced by : 1 -> Lov_Id.LOV_LOV
___ LOV_LOV                         referenced by : 1 -> Item_Id.LOV_ITEM
______ LOV_ITEM                        referenced by : 1 -> Module.LOV_BLOCK
______ LOV_ITEM                        referenced by : 2 -> Bloc.LOV_BLOCK
_________ LOV_BLOCK                       referenced by : 1 -> Mod_Nom_Module.LOV_MODULE

-----------------------------------------------------------------------------
-- Integrity referential constraints on table : LOV_ELEMENT_COLONNE.GENLOV --
-----------------------------------------------------------------------------
LOV_ELEMENT_COLONNE             referenced by : 1 -> Id.LOV_LOV
___ LOV_LOV                         referenced by : 1 -> Item_Id.LOV_ITEM
______ LOV_ITEM                        referenced by : 1 -> Module.LOV_BLOCK
______ LOV_ITEM                        referenced by : 2 -> Bloc.LOV_BLOCK
_________ LOV_BLOCK                       referenced by : 1 -> Mod_Nom_Module.LOV_MODULE

------------------------------------------------------------------
-- Integrity referential constraints on table : LOV_ITEM.GENLOV --
------------------------------------------------------------------
LOV_ITEM                        referenced by : 1 -> Module.LOV_BLOCK
LOV_ITEM                        referenced by : 2 -> Bloc.LOV_BLOCK
___ LOV_BLOCK                       referenced by : 1 -> Mod_Nom_Module.LOV_MODULE
------------------------------------------------------------------
LOV_ITEM                        references  : 1 -> Id.LOV_LOV  (CASCADE)

-----------------------------------------------------------------
-- Integrity referential constraints on table : LOV_LOV.GENLOV --
-----------------------------------------------------------------
LOV_LOV                         referenced by : 1 -> Item_Id.LOV_ITEM
___ LOV_ITEM                        referenced by : 1 -> Module.LOV_BLOCK
___ LOV_ITEM                        referenced by : 2 -> Bloc.LOV_BLOCK
______ LOV_BLOCK                       referenced by : 1 -> Mod_Nom_Module.LOV_MODULE
-----------------------------------------------------------------
LOV_LOV                         references  : 1 -> Id.LOV_COLONNE  (CASCADE)
LOV_LOV                         references  : 1 -> Id.LOV_ELEMENT_COLONNE  (CASCADE)

--------------------------------------------------------------------
-- Integrity referential constraints on table : LOV_MODULE.GENLOV --
--------------------------------------------------------------------
--------------------------------------------------------------------
LOV_MODULE                      references  : 1 -> Nom_Module.LOV_BLOCK  (CASCADE)

---------------------------------------------------------------
-- Integrity referential constraints on table : TRACE.GENLOV --
---------------------------------------------------------------

--------------------------------------------------------------------
-- Integrity referential constraints on table : UTIL_PREFS.GENLOV --
--------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL>

Francois