How to list all the referential integrities within tables in a schema 2

[mpramods]

I need to pull data from some production tables and load into the tables on test region. But the tables have referential integrity among them. I am using Toad and I have to check each table details individually(so that I load the parent table before the child table). This is taking a long time.

Is there any easy way to query and list all the referential integrities within the tables under a particular schema.


Thanks,
Pramod

 

[SantaMufasa]

Pramod,

If your objective is to load parent tables before child tables, then the following script (and its output should be helpful). The output of the script is a text-based hierarchical diagram of the parent/child relationships in a schema. (I call the script PK_FK.sql, since it hierarchically matches Primary Keys to Foreign Keys.)

Section 1 -- "PK_FK.sql" script:

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set pagesize 500
set feedback off
drop table uc
/
create table uc as
select table_name, constraint_name,
r_constraint_name from user_constraints
where constraint_type in ('P','R')
/
drop table pk_fk
/
create table pk_fk as
select decode(table_name,prior table_name,'(recursive)',table_name) detail,
 prior table_name master
from uc
where prior table_name is not null
connect by prior constraint_name =
r_constraint_name
union
select
table_name detail, '' master
from uc
where not exists
 (select 'x' from user_constraints
 where uc.table_name = table_name
   and constraint_type = 'R')
/
set pagesize 0
select
lpad(' ',(level-1)*3) ||
detail
from pk_fk
where master <> detail or master is null
connect by prior detail = master
start with master is null
/
set echo on
set feedback on
set pagesize 35

Section 2 -- Invocation and output of "PK_FK.sql":

@pk_fk

S_IMAGE
   S_PRODUCT
      S_INVENTORY
      S_ITEM
S_LONGTEXT
   S_PRODUCT
      S_INVENTORY
      S_ITEM
S_REGION
   S_CUSTOMER
      S_ORD
         S_ITEM
   S_DEPT
      S_EMP
         (recursive)
         S_CUSTOMER
            S_ORD
               S_ITEM
         S_ORD
            S_ITEM
         S_WAREHOUSE
            S_INVENTORY
   S_WAREHOUSE
      S_INVENTORY
S_TITLE
   S_EMP
      (recursive)
      S_CUSTOMER
         S_ORD
            S_ITEM
      S_ORD
         S_ITEM
      S_WAREHOUSE
         S_INVENTORY

The left-most tables are parents to the tables indented below them. In the example, above, the "S_EMP" table has a foreign key ("Manager_ID") that points to the Primary Key of the same that...that is why "(recursive)" appears below "S_EMP".

Let us know if this is useful to you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[mpramods]

SantaMufasa,

The script works perfect. This is exactly what I had been looking for. Mufasa you Rock....

Cheers,
Pramod

 

[ddiamond]

Awsome script, Mufasa!