Re: table referenced by
- From: jose soares <jose.soares@xxxxxxxxxxxxxx>
- To: oratune@xxxxxxxxx
- Date: Mon, 30 Apr 2012 12:18:13 +0200
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@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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
