[Oracle-l] table referenced by

Rodrigo Mufalani

at Apr 28, 2012 at 7:30 pm

Hi Jo,

I download this script from Tom kyte's blog and adjust to use DBA_XXXX
views instead USER_XXXXX views at
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:863253291
155

----------------------------- generating ddl from FK
-----------------------------
SET TIMING OFF
SET TIME OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET TRIMSPOOL ON
SET PAGES 0
column fkey format a80 word_wrapped

ACCEPT SCHEMA PROMPT 'Enter with owner: '
ACCEPT tabela PROMPT 'Enter with table_name: '

SPOOL drop_fk1.SQL
select
'alter table '||child_owner||'."'|| child_tname || '" drop constraint "'||
child_cons_name || '";' fkey
from
( select a.owner child_owner , a.table_name child_tname, a.constraint_name
child_cons_name,
b.r_constraint_name parent_cons_name,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
child_columns
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.owner, a.table_name, a.constraint_name, b.r_constraint_name )
child,
( select a.owner parent_owner, a.constraint_name parent_cons_name,
a.table_name parent_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.owner, a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = upper('&TABELA')
and parent.parent_owner= upper('&SCHEMA') ;
SPOOL OFF

SPOOL create_fk1.SQL
select
'alter table '|| child_owner ||'."'|| child_tname || '" add constraint "'||
child_cons_name || '" foreign key ( ' || child_columns || ' ) references
'|| parent_owner ||'."'|| parent_tname || '" ( ' || parent_columns || ');'
CR_fkey
from
( select a.owner child_owner , a.table_name child_tname, a.constraint_name
child_cons_name,
b.r_constraint_name parent_cons_name,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
child_columns
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.owner, a.table_name, a.constraint_name, b.r_constraint_name )
child,
( select a.owner parent_owner, a.constraint_name parent_cons_name,
a.table_name parent_tname,
max(decode(position, 1, '"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||
substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||
substr(column_name,1,30)||'"',NULL))
parent_columns
from dba_cons_columns a, dba_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.owner, a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = upper('&TABELA')
and parent.parent_owner= upper('&SCHEMA') ;
SPOOL OFF;

--ED drop_fk1.SQL
--ED create_fk1.SQL

Best Regards,

Rodrigo Mufalani
Cel.: +55 21 88994817
rodrigo@mufalani.com.br
www.mufalani.com.br
twitter: @mufalani

-----Mensagem original-----
De: oracle-l-bounce@freelists.org Em
nome de jo
Enviada em: sábado, 28 de abril de 2012 16:05
Para: ORACLE-L
Assunto: 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

reply | permalink

David Fitzjarrell

at Apr 28, 2012 at 10:18 pm

⇧

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@sferacarta.com>
To: ORACLE-L <oracle-l@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

reply | permalink

Jo

at Apr 29, 2012 at 3:07 pm

⇧

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@sferacarta.com>
*To:* ORACLE-L <oracle-l@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

reply | permalink

David Fitzjarrell

at Apr 29, 2012 at 6:27 pm

⇧

I wrote the query while connected as a DBA account; ALL_CONSTRAINTS or USER_CONSTRAINTS should work.

David Fitzjarrell

________________________________
From: jo <jose.soares@sferacarta.com>
To: David Fitzjarrell <oratune@yahoo.com>
Cc: ORACLE-L <oracle-l@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_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@sferacarta.com>
*To:* ORACLE-L <oracle-l@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-l

reply | permalink

Jose soares

at Apr 30, 2012 at 1:01 pm

⇧

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@sferacarta.com>
To: ORACLE-L <oracle-l@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

reply | permalink

Howard Latham

at Apr 29, 2012 at 4:41 pm

⇧

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@sferacarta.com>
*To:* ORACLE-L <oracle-l@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-l

reply | permalink

group	oracle-l @
categories	oracle
posted	Apr 28, '12 at 7:05p
active	Apr 30, '12 at 1:01p
posts	7
users	4
website	oracle.com

responses	expanded	Hotkey: s
font	variable	Hotkey: f
user style	avatars	Hotkey: a

[Oracle-l] table referenced by

Search Discussions

Related Discussions

4 users in discussion