FAQ
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

Search Discussions

  • 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
  • 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
  • Jo at Apr 29, 2012 at 3:07 pm
    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
  • 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
  • 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
  • Howard Latham at Apr 29, 2012 at 4:41 pm
    The are both views


    Sent from my Nascom I
    From: jo
    Sent: 29/04/2012 16:08
    To: David Fitzjarrell
    Cc: ORACLE-L
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 28, '12 at 7:05p
activeApr 30, '12 at 1:01p
posts7
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase