Re: table referenced by

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Sat, 28 Apr 2012 15:17:29 -0700 (PDT)
Message-ID: <1335651449.85081.YahooMailNeo_at_web160903.mail.bf1.yahoo.com>



Why go through all of that work when this produces a similar report:
 

SQL> select p.table_name parent_table, r.table_name ref_table, r.constraint_name
  2  from dba_constraints p join dba_constraints r on p.constraint_name = r.r_constraint_name
  3  where p.table_name = 'WWV_MIG_PROJECTS'
  4  /
PARENT_TABLE                   REF_TABLE                      CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
WWV_MIG_PROJECTS               WWV_MIG_ACCESS                 WWV_MIG_ACC_FK
WWV_MIG_PROJECTS               WWV_MIG_GENERATED_APPLICATIONS WWV_MIG_GEN_APP_PROJ_ID_FK
WWV_MIG_PROJECTS               WWV_MIG_PROJECT_COMPONENTS     WWV_MIG_PROJ_COMP_FK
WWV_MIG_PROJECTS               WWV_MIG_PROJECT_TRIGGERS       WWV_MIG_PROJ_TRIG_FK
WWV_MIG_PROJECTS               WWV_MIG_FORMS                  WWV_MIG_FORMS_PROJECT_ID_FK
WWV_MIG_PROJECTS               WWV_MIG_PLSQL_LIBS             WWV_MIG_PLLS_PROJECT_ID_FK
WWV_MIG_PROJECTS               WWV_MIG_FRM_REV_APEX_APP       WWV_MIG_FRM_REV_APEX_APP_FK
WWV_MIG_PROJECTS               WWV_MIG_RPTS                   WWV_MIG_RPTS_PROJECT_ID_FK
WWV_MIG_PROJECTS               WWV_MIG_FRM_MENUS              WWV_MIG_MENUS_PROJECT_ID_FK
WWV_MIG_PROJECTS               WWV_MIG_OLB                    WWV_MIG_OLB_PROJECT_ID_FK
10 rows selected.
Substitute your table name for the one in the example query.

David Fitzjarrell



From: jo <jose.soares_at_sferacarta.com> To: ORACLE-L <oracle-l_at_freelists.org> Sent: Saturday, April 28, 2012 1:04 PM
Subject: table referenced by

Hi all,

I'm trying to create a query to know which tables are linked with table 'anagrafica'
in PostgreSQL I can achieve it with this query:

SELECT 'anagrafica' AS table,
conrelid::pg_catalog.regclass AS referenced_by, conname AS foreignkey_name
FROM pg_catalog.pg_constraint c
WHERE c.contype = 'f'
AND c.confrelid = (
    SELECT c.oid FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relname = 'anagrafica' AND

pg_catalog.pg_table_is_visible(c.oid)
    )

  table    |      referenced_by      |                  foreignkey_name

------------+--------------------------+----------------------------------------------------
anagrafica | asl                      | asl_id_anagrafica_fkey anagrafica | azienda                  | azienda_id_anagrafica_fkey

Is it possible to do do the same thing in Oracle?

j

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Apr 28 2012 - 17:17:29 CDT

Original text of this message