2 Replies Latest reply: Oct 4, 2013 12:43 PM by EdStevens RSS

    find parent table

    natpidgeon

      i want to find the parent table for a column, the column name is property_id and is in the table P_segment.

      how do i find the parent table?

        • 1. Re: find parent table
          Frank Kulash

          Hi,

           

          You can query the data dictionary views ALL_CONSTRAINTS and ALL_CONS_COLUMNS, like this

          SELECT  parent.owner

          ,       parent.table_name

          FROM    all_constraints   parent

          JOIN    all_constraints   child  ON  child.r_owner           = parent.owner

                                           AND child.r_constraint_name = parent.constraint_name

          JOIN    all_cons_columns  acc    ON  acc.constraint_name     = child.constraint_name

          WHERE   acc.owner         = 'XYZ'

          AND     acc.table_name    = 'P_SEGMENT'

          AND     acc.column_name   = 'PROPERTY_ID'

          ;

          where XYZ is the owner of the child table.

          • 2. Re: find parent table
            EdStevens

            FrankKulash wrote:

             

            Hi,

             

            You can query the data dictionary views ALL_CONSTRAINTS and ALL_CONS_COLUMNS, like this

            SELECT  parent.owner

            ,       parent.table_name

            FROM    all_constraints   parent

            JOIN    all_constraints   child  ON  child.r_owner           = parent.owner

                                             AND child.r_constraint_name = parent.constraint_name

            JOIN    all_cons_columns  acc    ON  acc.constraint_name     = child.constraint_name

            WHERE   acc.owner         = 'XYZ'

            AND     acc.table_name    = 'P_SEGMENT'

            AND     acc.column_name   = 'PROPERTY_ID'

            ;

            where XYZ is the owner of the child table.

             

            Assuming, of course, the relationship and integrity are defined and enforced by the database and not by application code.   (Says the guy who has seen too many databases designed by developers who treat the DB as a data dump.  )