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

Thread: Relationship info

  1. #1
    Join Date
    Sep 2002
    Posts
    376

    Relationship info

    Hi,
    Which view i can use to see the relationships between the tables. I mean the PK and FK relationship b/n all the tables in the db.

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    dba_constraints
    dba_cons_columns
    -- Dilip

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Use ALL_CONS_COLUMNS, ALL_CONSTRAINTS

    or use

    hr@ACME.WORLD> @list_cons.sql TABLE_NAME

    list_cons.sql

    Code:
    --DESCRIPTION
    --List all the constraints on a table.
    --
    --INPUTS
    --  Param 1   A table name, optionally qualified
    --            optionally qualified by an owner name.
    --            For example: "SYS.ALL_TAB_COLUMNS".
    --
    SET ECHO OFF
    SET NEWPAGE 1
    SET VERIFY OFF
    SET FEEDBACK OFF
    SET HEADING OFF
    SET PAGESIZE 9999
    SET RECSEP OFF
    SET CONCAT ON
    CLEAR BREAKS
    CLEAR COMPUTES
    CLEAR COLUMNS
    TTITLE OFF
    BTITLE OFF
    
    --Disect the input argument, and get the owner name and
    --table name into two seperate substitution variables.
    --The owner name defaults to the current user.
    DEFINE s_owner_name = ' '
    DEFINE s_table_name = ' '
    COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
    COLUMN table_name NOPRINT NEW_VALUE s_table_name
    SELECT DECODE (INSTR ('&&1', '.'),
                   0, USER,                            /*Default to current user.*/
                   UPPER (SUBSTR ('&&1', 1, INSTR ('&&1', '.') - 1))
                  ) owner_name,
           DECODE (INSTR ('&&1', '.'),
                   0, UPPER ('&&1'),         /*Only the table name was passed in.*/
                   UPPER (SUBSTR ('&&1', INSTR ('&&1', '.') + 1))
                  ) table_name
      FROM DUAL;
    
    --Clear column definitions so that no future queries
    --affect the substitution variables that were just set.
    CLEAR COLUMNS
    
    --Format the columns
    COLUMN constraint_name NOPRINT NEW_VALUE constraint_name_var
    COLUMN constraint_type_desc NOPRINT NEW_VALUE constraint_type_var
    COLUMN r_owner NOPRINT NEW_VALUE r_owner_var
    COLUMN r_table_name NOPRINT NEW_VALUE r_table_name_var
    COLUMN status NOPRINT NEW_VALUE status_var
    COLUMN delete_rule_desc NOPRINT NEW_VALUE delete_rule_var
    COLUMN search_condition FORMAT A50 WORD_WRAPPED
    COLUMN column_name FORMAT A30
    COLUMN r_column_name FORMAT A30
    
    --This script breaks on each constraint, and each constraint
    --has its own page title. The page title shows information 
    --relating to the constraint as a whole.
    BREAK ON constraint_name SKIP PAGE
    
    --Tell the user he is going to see constraints. 
    PROMPT CONSTRAINTS ON TABLE &&s_owner_name..&&s_table_name
    
    --First show the primary key and unique constraints.
    --
    --Setup the page title. 
    TTITLE LEFT '   ' constraint_name_var ' ' constraint_type_var -
           ' ' status_var SKIP 1
    
    --Run the query to display primary key and unique constraints.
    SELECT   ac.constraint_name,
             DECODE (ac.constraint_type,
                     'P', 'PRIMARY KEY',
                     'U', 'UNIQUE'
                    ) constraint_type_desc,
             ' ' table_name, '      ' spacer, acc.column_name, ac.status
        FROM all_constraints ac, all_cons_columns acc
       WHERE ac.owner = '&&s_owner_name'
         AND ac.table_name = '&&s_table_name'
         AND ac.owner = acc.owner
         AND ac.constraint_name = acc.constraint_name
         AND ac.constraint_type IN ('P', 'U')
    ORDER BY DECODE (ac.constraint_type, 'P', 1, 'U', 2),
             ac.constraint_name,
             acc.POSITION;
    
    --Then show the foreign key constraints
    --
    --Setup the page title for the foreign key constraints
    TTITLE LEFT '   '  constraint_name_var ' ' status_var SKIP 1 -
           '   FOREIGN KEY TO ' r_owner_var '.' r_table_name_var SKIP 1
    
    --Run the query to show foreign key constraints
    SELECT   ac.constraint_name, '      ' indent, acc.column_name,
             r_acc.owner r_owner, r_acc.table_name r_table_name,
             r_acc.column_name r_column_name
        FROM all_constraints ac, all_cons_columns acc, all_cons_columns r_acc
       WHERE ac.owner = '&&s_owner_name'
         AND ac.table_name = '&&s_table_name'
         AND ac.constraint_type = 'R'
         AND ac.owner = acc.owner
         AND ac.constraint_name = acc.constraint_name
         AND ac.r_owner = r_acc.owner
         AND ac.r_constraint_name = r_acc.constraint_name
         AND acc.POSITION = r_acc.POSITION
    ORDER BY ac.constraint_name, acc.POSITION;
    
    --Then show the check constraints
    --
    --Setup the page title for the check constraints
    TTITLE LEFT '   '  constraint_name_var ' CHECK ' status_var SKIP 1
    
    SELECT ac.constraint_name, '      ' spacer, search_condition
      FROM all_constraints ac
     WHERE ac.owner = '&&s_owner_name'
       AND ac.table_name = '&&s_table_name'
       AND ac.constraint_type = 'C';
    
    --Undefine variables and restore settings to their defaults.
    UNDEFINE s_owner_name
    UNDEFINE s_table_name
    
    SET NEWPAGE 1
    SET VERIFY ON
    SET FEEDBACK ON
    SET HEADING ON
    SET PAGESIZE 14
    SET RECSEP WRAPPED
    CLEAR BREAKS
    CLEAR COMPUTES
    CLEAR COLUMNS
    TTITLE OFF
    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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