In the company I work at, the database is massive. We have a lot of tables and sometimes when I am using SQL to search for something i.e. what status a particular Purchase Order is in. I often find that the column the status is stored in only contains a number (therefore the column is a foreign key).
Q: I'd like to know how to find the table where this foreign key referring to. Without using the diagram as its massive and I can't tell the relationships.
e.g.
Table: Purchase Orders
Column: PO_Status
PO_Status only contains number 1-10.
The name and description of the status is stored in another table and the number 1-10 is referring to that record in that other table. I want to know what that table name is.
This will help save a lot of time. Please Help!
You can interrogate Oracle's "metadata" :
SELECT c.table_name, c.constraint_name, c2.table_name "REFERENCED_TABLE"
FROM all_constraints c
INNER JOIN all_constraints c2
ON c.r_constraint_name = c2.constraint_name
WHERE c.table_name = 'YOUR_TABLE_NAME';
Also, keep in mind that some value restrictions can be enforced using CHECK instead of implying the presence of a FK.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments