RE: table referenced by

The are both views


Sent from my Nascom I
From: jo
Sent: 29/04/2012 16:08
To: David Fitzjarrell
Cc: ORACLE-L
Subject: Re: table referenced by
It works but the table name in my db is all_constraints.
Thanks David,
j

SELECT p.table_name parent_table, r.table_name ref_table, r.constraint_name
FROM all_constraints p join all_constraints r on p.constraint_name =
r.r_constraint_name
WHERE p.table_name = 'ANAGRAFICA'

parent_table | ref_table | constraint_name
------------ + ------------------------ + ----------------------------
ANAGRAFICA | ASL | SYS_C005900
ANAGRAFICA | AZIENDA | SYS_C006000
ANAGRAFICA | CLIENTI_RAGIONERIA | SYS_C006859
ANAGRAFICA | DIPARTIMENTO | SYS_C005988
ANAGRAFICA | DISTRETTO | SYS_C005918
ANAGRAFICA | ENTE | SYS_C005942
ANAGRAFICA | FIGURA_AZIENDALE | SYS_C006146
ANAGRAFICA | ISCRIZIONE_ALIMENTARISTA | SYS_C006869
ANAGRAFICA | MORSICATURA | SYS_C005978
ANAGRAFICA | OPERATORE | OPERATORE_ID_ANAGRAFICA_FKEY
ANAGRAFICA | SERVIZIO | SYS_C006019
ANAGRAFICA | UNITA_AZIENDALE | SYS_C006032


David Fitzjarrell wrote:
> 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@xxxxxxxxxxxxxx>
> *To:* ORACLE-L <oracle-l@xxxxxxxxxxxxx>
> *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
>
>
>
>


-- 
Jose Soares Da Silva                     _/_/
Sferacarta Net
Via Bazzanese 69                       _/_/    _/_/_/
40033 Casalecchio di Reno             _/_/  _/_/  _/_/
Bologna - Italy                      _/_/  _/_/  _/_/
Ph  +39051591054              _/_/  _/_/  _/_/  _/_/
fax +390516131537            _/_/  _/_/  _/_/  _/_/
web:www.sferacarta.com        _/_/_/      _/_/_/

Le informazioni contenute nella presente mail ed in ogni eventuale
file allegato sono riservate e, comunque, destinate esclusivamente
alla persona o ente sopraindicati, ai sensi del decreto legislativo 30
giugno 2003, n. 196. La diffusione, distribuzione e/o copiatura della
mail trasmessa, da parte di qualsiasi soggetto diverso dal
destinatario, sono vietate. La correttezza, l’integrità e la sicurezza
della presente mail non possono essere garantite. Se avete ricevuto
questa mail per errore, Vi preghiamo di contattarci immediatamente e
di eliminarla. Grazie.

This communication is intended only for use by the addressee, pursuant
to legislative decree 30 June 2003, n. 196. It may contain
confidential or privileged information. You should not copy or use it
to disclose its contents to any other person. Transmission cannot be
guaranteed to be error-free, complete and secure. If you are not the
intended recipient and receive this communication unintentionally,
please inform us immediately and then delete this message from your
system. Thank you.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l


Other related posts: