This discussion is archived
7 Replies Latest reply: Jun 18, 2013 9:50 AM by Lubiez Jean-Valentin RSS

How to find all references on a table

ed0f625b-6857-4956-9b66-da280b7cf3a2 Newbie
Currently Being Moderated

I have been going through the details provided for PO_HEADERS_ALL table in oracle etrm. In the comments column of description section they have specified that a particular column refers to another column of some table.

 

For eg.

AGENT_IDNUMBER(9)YesBuyer unique identifier. (References PO_AGENTS.agent_id)

 

Does oracle implements any constraints on this reference in the database?

 

What I am looking for is to find out all the columns in po_headers_all that references columns of another table.

  • 1. Re: How to find all references on a table
    EdStevens Guru
    Currently Being Moderated

    ed0f625b-6857-4956-9b66-da280b7cf3a2 wrote:

     

    I have been going through the details provided for PO_HEADERS_ALL table in oracle etrm. In the comments column of description section they have specified that a particular column refers to another column of some table.

     

    For eg.

    AGENT_ID NUMBER (9) Yes Buyer unique identifier. (References PO_AGENTS.agent_id)

     

    Does oracle implements any constraints on this reference in the database?

     

    What I am looking for is to find out all the columns in po_headers_all that references columns of another table.

    In itself a comment means very little.  It means nothing at all to the database.  That specific comment would make me look at DBA_CONSTRAINTS, looking particularly for any foreign keys.

     

    In answer to your broader question "how to find all references on a table", you need to be aware that any reference could be in any code, anywhere.  It doesn't have to be in a package or procedure in the database.  It can be in the application code outside the database. It can be in a sql script buried somewhere on the server on on someone's desktop.  And the application designers may have (foolishly) decided to forgo the power of the database and bake all of their constraints into the application code.

  • 2. Re: How to find all references on a table
    ed0f625b-6857-4956-9b66-da280b7cf3a2 Newbie
    Currently Being Moderated

    Thanks for quick response...

     

    I checked the dba_constraints and it has no foreign key associated...

     

    Just wanted to confirm on this as it was hard for me to believe that oracle has no particular way to track back which column of the table is referring to what other column.

  • 3. Re: How to find all references on a table
    SHANOJ Newbie
    Currently Being Moderated

    ---- Show all constraints on a table

    col type format a10
    col cons_name format a30
    select decode(constraint_type,
      'C', 'Check',
      'O', 'R/O View',
      'P', 'Primary',
      'R', 'Foreign',
      'U', 'Unique',
      'V', 'Check view') type
    , constraint_name cons_name
    , status
    , last_change
    from dba_constraints
    where owner like '&owner'
    and table_name like '&table_name'
    order by 1
    /

  • 4. Re: How to find all references on a table
    ed0f625b-6857-4956-9b66-da280b7cf3a2 Newbie
    Currently Being Moderated

    Hi Shanoj,

     

    Even though etrm says that a column refers to another column of the table, there is no foreign key associated with it.

  • 5. Re: How to find all references on a table
    EdStevens Guru
    Currently Being Moderated

    ed0f625b-6857-4956-9b66-da280b7cf3a2 wrote:

     

    Thanks for quick response...

     

    I checked the dba_constraints and it has no foreign key associated...

     

    Just wanted to confirm on this as it was hard for me to believe that oracle has no particular way to track back which column of the table is referring to what other column.

    The database can only keep track of relationships and constraints that are defined in the database.  If the app developers and/or DBA choose to not define those relationships and/or  constraints, foolishly choosing instead to enforce them in application code, then how do you expect the database to know about that?

  • 6. Re: How to find all references on a table
    Martin Preiss Expert
    Currently Being Moderated

    if there is only a logical connection between two tables no RDBMS will be able to tell you something about the relation (at least no RDBMS I know). The definition of these relations is the job of database constraints. You may be lucky to find the code that joins the data in the dictionary view DBA_SOURCE (or the variants for USER_ or ALL_) - but as Ed Stevens already wrote: if the logical connection is defined in - hand made (since ORM tools usually define the constraints) - application code then you have to find this code (or the developer who wrote it ...)

     

    Regards

     

    Martin

  • 7. Re: How to find all references on a table
    Lubiez Jean-Valentin Guru
    Currently Being Moderated

    Hello,

     

     

    Just wanted to confirm on this as it was hard for me to believe that oracle has no particular way to track back which column of the table is referring to what other column.

     

    The View DBA_CONS_COLUMNS will give you the Columns specified in a Constraint:

     

    http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_1042.htm

     

    The columns CONSTRAINT_NAME exists also in the View DBA_CONSTRAINTS. So by using these 2 Views (DBA_CONS_COLUMNS and DBA_CONSTRAINTS) you may have the information you are looking for, provided that your "Constraints" is defined in Oracle.

     

     

    Hope this help.

    Best Regards,

    Jean-Valentin Lubiez

     

    Message was edited by: LubiezJean-Valentin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points