How to view the all relationship between tables if I don't know h ow many relationships

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Mon, 10 Mar 2003 13:57:30 -0000
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703676C04@lnewton.leeds.lfs.co.uk>

Assuming you have access to SYSTEM you can see all relationships between tables (even if in different schemas) as follows :

First, get all the Referential Integrity (foreign key) constraints :

SELECT OWNER,

        CONSTRAINT_NAME, 
        TABLE_NAME, 
        R_OWNER, 
        R_CONSTRAINT_NAME 

FROM dba_constraints
WHERE owner = 'CMDB'
AND constraint_type = 'R';

OWNER : The owner of the table
CONSTRAINT_NAME : Obvious :o)
TABLE_NAME : Teh table, under owner, which has the constraint defined against.
R_OWNER : Owner of the table that this one is constrained against R_CONSTRAINT_NAME : the constraint name on the other table, which this one relates to. Not the table name !

An example :

I have a table BUILD which is the 'one' and another table CONFIGITEM which is the 'many'.
Table BUILD is primary keyed on a single integer value. Table CONGFIGITEM has a column BUILD_ID which relates it back to the 'one' table via the foreign key constraint.

Assuming that I want to find out the above, I do the above select on the CONFIGITEM table only (add and table_name = 'CONFIGITEM' to the where clause). This returns this (amongst other stuff) :

OWNER CONSTRAINT_NAME TABLE_NAME R_OWNER R_CONSTRAINT_NAME
CMDB FK_CONFIGITEM_BUILD CONFIGITEM CMDB PK_BUILD So there is a relationship called FK_CONFIGITEM_BUILD and table CONFIGITEM is constrained by table BUILD via the constraint on BUILD called PK_BUILD. Because this is an 'R' type constraint, CONFIGITEM has to be the 'many' table. So now all I have to do is find out what PK_BUILD is defined on :

SELECT table_name
FROM dba_constraints
WHERE owner = 'CMDB'
AND constraint_NAME = 'PK_BUILD';

And the result of that is a table called BUILD. This is the 'one' table. So now I have the 'one' and the 'many' tables, how do I find out the column(s) they are related by ?

SELECT column_name,

        position
FROM dba_cons_columns
WHERE owner = 'CMDB'
AND constraint_NAME = 'PK_BUILD'
ORDER BY position;

That returns the columns for the 'one' tables and shows me this result :

COLUMN_NAME	POSITION
BUILD_ID	1

And fianlly, for the 'many' table :

SELECT column_name,

        position
FROM dba_cons_columns
WHERE owner = 'CMDB'
AND constraint_NAME = 'FK_CONFIGITEM_BUILD' ORDER BY position;

Which gives the following :

COLUMN_NAME	POSITION
CONFIGITEM_BUILD_ID	1

So, my 'one' table is BUILD
The 'many' table is CONFIGITEM
They are joined by a relationship called FK_CONFIGITEM_BUILD using a column on CONFIGITEM called CONFIGITEM_BUILD_ID and the corresponding column on the BUILD table is called BUILD_ID.

Repeat for all other tables in the schema !

To save some work, I have a utility 'what I wrote' some time back which does all of the above and spits out some HTML. No fancy diagrams etc, just words as above. If you want it, email me privately.

Cheers,
Norman.

PS. Of course, if your schema has been designed without any foreign key constraints, you might need to look in the source code for any triggers that are attached to your tables - Referential Integrity could be done that way - and failing that, it will be done in your application. If so, run a mile :o)

-----Original Message-----
From: wfalai_at_sinaman.com (Fai) [mailto:wfalai_at_sinaman.com] Posted At: Monday, March 10, 2003 8:51 AM Posted To: server
Conversation: How to view the all relationship between tables if I don't know how many relationships
Subject: How to view the all relationship between tables if I don't know how many relationships

How to view the all relationships between tables if I don't know how many relationships? Received on Mon Mar 10 2003 - 07:57:30 CST