DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Parent-Child

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hi:
    I want to know the child tables first and the parent tables. Is there a query where i can get something like this. If there is a procedure that is also good but I want to be able to enter the schema or owner name and then should be able to list the tables as per children first and then parents..
    Thanks

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Thumbs up


    Use a tool like TOAD.
    Those are very helpful

    - Magnus

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    You just want to know the child table names?

    Normally if I dont use GUI tool I find out the PK of parent tables then using dba_constraints I find the child tables

    select table_name
    from dba_constraints where r_constraint_name='PARENT_TABLE_PK'

    dunno if this helps you

  4. #4
    Join Date
    Feb 2000
    Location
    New York,U.S.A.
    Posts
    245
    set echo off
    set verify off
    col TABLE_NAME format a30
    col COLUMN_NAME format a20
    col POSITION format a30
    Try the following script. It will ask you for your tablename.when you supply tablename, it will show the parent table of the tablename or the child table of the tablename you supplied.

    dragon


    accept xTable prompt 'Enter Table Name: '
    TTITLE LEFT 'Child Tables for the table: '&xTABLE
    break on TABLE_NAME
    SELECT B.TABLE_NAME, C.COLUMN_NAME, C.POSITION
    FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C
    WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
    AND A.TABLE_NAME = C.TABLE_NAME
    AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
    AND A.TABLE_NAME = UPPER('&xTable')
    ORDER BY B.TABLE_NAME, C.POSITION;
    TTITLE LEFT 'Parent tables for the table: '&xTable
    SELECT A.TABLE_NAME, C.COLUMN_NAME, C.POSITION
    FROM USER_CONSTRAINTS A, USER_CONSTRAINTS B, USER_CONS_COLUMNS C
    WHERE A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
    AND B.TABLE_NAME = C.TABLE_NAME
    AND B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
    AND B.TABLE_NAME = UPPER('&xTable')
    ORDER BY A.TABLE_NAME, C.POSITION;


  5. #5
    Join Date
    Oct 2000
    Posts
    449
    Not Just one tablename and its parent or child..
    I want to be able to do it for an entire schema.. And this is not to simply see (using TOAD or whatever) but further insert into schema with constraints, so to avoid any problems..
    Thanks

  6. #6
    Join Date
    Feb 2001
    Posts
    203
    select b.table_name Child_table,a.table_name parent_table ,b.constraint_name,b.r_constraint_name
    from user_constraints b,user_constraints a
    where a.constraint_type='P' And
    a.constraint_name=b.r_constraint_name
    order by b.table_name;

    Try this query. It will give the child table_name,parent table_name,constraint_name,r_constraint_name. It will give order by Child table.
    sree

  7. #7
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Cool



    Well, if you are familiar with your data you are inserting then just DISABLE the FK constraints, INSERT the data, then ENABLE the constraints again.

    If one of the constraints cannot enable, then just run a diagnostic on the two tables.

    Is this just not feasable?

    - Magnus

  8. #8
    Join Date
    Oct 2000
    Posts
    449
    Yap.. Sree_sri.. It works.. Great .. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width