I want select all Reference tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: I want select all Reference tables

Hybrid View

  1. #1
    Join Date
    Jun 2002
    Posts
    8

    Question

    Hi ,
    I want to select all the table names which have foreign key relationship with the master tables.Can any one give solution for this?

    Thx,
    Selva

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    look dba_constraints, dba_cons_columns

  3. #3
    Join Date
    Jun 2002
    Posts
    8
    Thanks for your reply.
    I Look at dba_constraints, dba_cons_columns.
    Can you give query to select all tables which References to master table. For example i have d_sec_account as a master table and a1,a2,a3 ,a4 are references to d_sec_account tables. My query will returen a1,a2,a3,a4 tables name.

    Thx,
    Selva

  4. #4
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    Wink

    Hi ,
    As per my knowledge . different naming conventions is usually set for master tables and transaction tables . it depends on the design stratagy .
    If naming conventions are not set . then just confirm from your designer if all the tables without foreign key and with primary key are master tables . if YES based on these where clause just query on the above tables .

  5. #5
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    select c.owner, c.table_name
    from dba_constraints c,
    dba_constraints m
    where c.constraint_type = 'R'
    and m.constraint_name = c.r_constraint_name
    and m.owner = 'OWNER_OF_MASTER_TABLE'
    and m.table_name = 'MASTER_TABLE'

  6. #6
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    The following procedure uses the following steps to enable or disable all of the FK constraints *TO* a specified table:
    Finds the PK of the specified table.
    Uses this PK to find all of the FKs that are linked to the PK.
    Puts together an ALTER TABLE DISABLE CONSTRAINT command to disable each FK.
    Uses dynamic SQL to execute the commands.


    PROMPT
    PROMPT Creating Procedure able_fk_constraints
    CREATE OR REPLACE PROCEDURE able_fk_constraints(
    pTable IN VARCHAR2 ,
    pAble IN VARCHAR2 )
    IS
    vPKName VARCHAR2(80);

    -- This cursor returns the list of FK constraints linked to the specified
    -- PK constraint.
    CURSOR curFK(pcPKName IN VARCHAR2) IS
    SELECT constraint_name, table_name
    FROM user_constraints
    WHERE r_constraint_name = pcPKName;

    -- These two variables are used for the dynamic SQL
    nDDLCursor INTEGER;
    nDDLReturn INTEGER;
    BEGIN
    /*******************************************************************************/
    /* ABLE_FK_CONSTRAINTS - This procedure easily enables/disables FK constraints */
    /* pointing TO the specified table. */
    /* */
    /* Parameters: pTable - The name of the table to dis/enable FK */
    /* constraints to. */
    /* pAble - One of: DISABLE or ENABLE */
    /*******************************************************************************/

    -- Get the name of the PK constraint for the specified table.
    BEGIN
    SELECT constraint_name INTO vPKName
    FROM user_constraints
    WHERE table_name = pTable
    AND constraint_type = 'P';
    END;


    -- Now get the FK constraints linked to the PK constraint of the specified table.
    FOR fk IN curFK(vPKName) LOOP

    -- Use dynamic SQL to execute the ALTER TABLE command and dis/enable the constraint
    nDDLCursor := dbms_sql.open_cursor;
    dbms_sql.parse(nDDLCursor,'ALTER TABLE '||fk.table_name||' '
    ||pAble||' CONSTRAINT '||fk.constraint_name, 1);
    nDDLReturn := dbms_sql.execute(nDDLCursor);
    dbms_sql.close_cursor(nDDLCursor);
    END LOOP;
    END ABLE_FK_CONSTRAINTS;
    /


    An example of using the script:
    execute able_fk_constraints('MYTABLE','DISABLE');
    truncate table mytable;
    @load_mytable
    execute able_fk_constraints('MYTABLE','ENABLE');

    "Greatest Rewards come only with Greatest Commitments!"

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