-1

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!

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Qasim Khan
  • 3
  • 1
  • 1
    Possible duplicate of [How to find foreign key dependencies in SQL Server?](https://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server) – Brian Tompsett - 汤莱恩 Dec 15 '17 at 14:36
  • This is easily googled if you would phrase your question clearly & concisely many ways. – philipxy Dec 16 '17 at 03:44
  • @BrianTompsett-汤莱恩 Hi Brian, didn't come across that link! Its exactly what I needed! Gave me a list of pk tables and the pk columns and also where those pk are also fk's and the tables they are in. Thank you~! – Qasim Khan Dec 20 '17 at 09:57

1 Answers1

-1

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.

Dessma
  • 599
  • 3
  • 11