Re: table referenced by
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Sun, 29 Apr 2012 11:26:31 -0700 (PDT)
Message-ID: <1335723991.97305.YahooMailNeo_at_web160901.mail.bf1.yahoo.com>
I wrote the query while connected as a DBA account; ALL_CONSTRAINTS or USER_CONSTRAINTS should work.
From: jo <jose.soares_at_sferacarta.com> To: David Fitzjarrell <oratune_at_yahoo.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Sent: Sunday, April 29, 2012 9:06 AM
Subject: Re: table referenced by
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_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
>
>
>
>
Date: Sun, 29 Apr 2012 11:26:31 -0700 (PDT)
Message-ID: <1335723991.97305.YahooMailNeo_at_web160901.mail.bf1.yahoo.com>
I wrote the query while connected as a DBA account; ALL_CONSTRAINTS or USER_CONSTRAINTS should work.
David Fitzjarrell
From: jo <jose.soares_at_sferacarta.com> To: David Fitzjarrell <oratune_at_yahoo.com> Cc: ORACLE-L <oracle-l_at_freelists.org> Sent: Sunday, April 29, 2012 9:06 AM
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_FKEYANAGRAFICA | 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_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
>
>
>
>
-- 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-lReceived on Sun Apr 29 2012 - 13:26:31 CDT