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 /
Substitute your table name for the one in the example query.
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
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_FKWWV_MIG_PROJECTS WWV_MIG_FRM_MENUS WWV_MIG_MENUS_PROJECT_ID_FK
WWV_MIG_PROJECTS WWV_MIG_OLB WWV_MIG_OLB_PROJECT_ID_FK10 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-lReceived on Sat Apr 28 2012 - 17:17:29 CDT