Re: table referenced by
- From: jo <jose.soares@xxxxxxxxxxxxxx>
- To: David Fitzjarrell <oratune@xxxxxxxxx>
- Date: Sun, 29 Apr 2012 17:06:41 +0200
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
>
> --
> //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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
