Re: table referenced by

From: jose soares <jose.soares_at_sferacarta.com>
Date: Mon, 30 Apr 2012 12:18:13 +0200
Message-ID: <4F9E66E5.6070504_at_sferacarta.com>

I'm trying to get the foreign key definition , but I don't know how. to..

I'm able to do this in postgres in this way:

SELECT

     conrelid::pg_catalog.regclass AS referenced_by,
     conname AS foreignkey_name
     pg_catalog.pg_get_constraintdef(c.oid, true) as definition
     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)
     )
          
   
referenced_by | foreignkey_name             | definition
--------------+------------------------------------------------------------------------------------ 

asl           | asl_id_anagrafica_fkey      | FOREIGN KEY 
(id_anagrafica) REFERENCES anagrafica(id)
azienda       | azienda_id_anagrafica_fkey  | FOREIGN KEY 

(id_anagrafica) REFERENCES anagrafica(id)

j

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
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Mon Apr 30 2012 - 05:18:13 CDT