Pl/SQL for all database table relationships

45 pts.
Tags:
Oracle PL/SQL
Oracle81
I need PL/SQL code to list all parent-child relationships of all my database tables. I want to list and table relationships, that is, primary and foreign keys.


Software/Hardware used:
Oracle 81,Oracle SQL Developer
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

No approved answer yet.

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    On current releases of IBM i, you can query SYSIBM.SQLPRIKEYS and SYSIBM.SQLFORKEYS.
    36,320 pointsBadges:
    report
  • llkhoutx

    Thanks for your replay, as a newbie, you suggestions doesn't work for me.

    I'm using Oracle SQL Developer on database 'XXX' and need to determine all table relationships.

    Any additional help is useful. Thank you again for your tolerance.

    45 pointsBadges:
    report
  • carlosdl
    May I ask what are you planning to do with that info?

    If you want it to somehow 'document' your database, you could easily create a relational model that would show you the relationship in a diagram.
    85,390 pointsBadges:
    report
  • llkhoutx

    I'll import the resulting desired relationships (table) into MS Access and make various searches on any of the various columns, i.e columns: primary_table, primary_key, foreign_table, foreign_key.

    Note that

    (1) I'm migrating a complex PL/SQL procedure to T-SQL. The tables are similar, but different, neither of which I constructed and neither of which is documented. Accordingly, I want to easily search relationships.

    (2) I'm using Oracle SQL Developer on a read-only database.

    I have created a similar Access database from the same information in a (slightly similar) SQL Server database.

    A visual representation would be helpful, but I need the relationship information for each table which I can easily query using combo boxes / pick lists. Multiple table have the same column names. The table names are semi mnemonic.

    Thank you for your response.

    45 pointsBadges:
    report
  • carlosdl
    This could serve as a starting point.  With the following query you can get the list of foreign keys and the tables they reference for one specific table.

    You could try modifying it to join the ALL_TABLES view, or you could us PL to loop through it if that serves better for your purpose.

    SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
           -- referenced pk
           c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
      FROM all_cons_columns a
      JOIN all_constraints c ON a.owner = c.owner
                            AND a.constraint_name = c.constraint_name
      JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                               AND c.r_constraint_name = c_pk.constraint_name
     where c.constraint_type = 'R'
       AND a.table_name = :TableName;

    85,390 pointsBadges:
    report
  • llkhoutx
    Thx carlosdi, I'll give it a try.
    45 pointsBadges:
    report
  • carlosdl
    This code was posted by user reckonankit, but was removed from the answer Wiki because it is T-SQL and won't work on an Oracle database.  This code is for SQL Server:

    CREATE PROCEDURE ShowRelationships
    @parent AS int
    AS
     PRINT 'Parent =  ' + CAST(@parent AS varchar(3))
    
     DECLARE @child AS int;
     DECLARE cur_children CURSOR
     FOR
      SELECT child
      FROM PCREL
      WHERE parent = @parent;
    
     OPEN cur_children;
     FETCH NEXT FROM cur_children INTO @child;
    
     IF (@child IS NULL)
     BEGIN
      PRINT CAST(@parent AS varchar(3)) + ' has no children...';
     END
    
     WHILE @@FETCH_STATUS = 0
     BEGIN
      PRINT 'Child = ' + CAST(@child AS varchar(3))
    
      FETCH NEXT FROM cur_children INTO @child;
     END
    
     CLOSE cur_children;
     DEALLOCATE cur_children;
    
    
     SELECT TOP 1 @child = child
     FROM PCREL
     WHERE parent = @parent;
    
     EXECUTE ShowRelationships @child;

    85,390 pointsBadges:
    report
  • llkhoutx
    I have a good SQL Server (T-SQL).
    45 pointsBadges:
    report
  • carlosdl
    Just in case: I just moved the above code from the Wiki.  I'm not the author and I don't think it will work even on SQL Server.
    85,390 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: