|
Schema: How do I find all the foreign keys in a database |
 |
 |
 |
|
Scritto da Lorenzo Bossoletti
|
|
Mercoledì 11 Gennaio 2012 08:14 |
|
How to find relationship between tables
know how to get tables that columns belong to, but how to I find out where
a parent table is, if one exists and what the foreign key to that table is?
Here is one query using the INFORMATION_SCHEMA views that returns both sides of all FOREIGN KEY relationships, as well as the name of the foreign key constraint.
SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN
(
SELECT
i1.TABLE_NAME, i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
-- optional:
ORDER BY
1,2,3,4 |
this is how I queried Oracle DB to get information about Foreign keys.
SELECT A.CONSTRAINT_NAME,
C.TABLE_NAME PARENT_TABLE,C.COLUMN_NAME PARENT_COLUMN,
A.TABLE_NAME CHILD_TABLE,B.COLUMN_NAME CHILD_COLUMN
FROM USER_CONSTRAINTS A
INNER JOIN USER_CONS_COLUMNS B
ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME
INNER JOIN USER_CONS_COLUMNS C
ON A.R_CONSTRAINT_NAME=C.CONSTRAINT_NAME
WHERE A.CONSTRAINT_TYPE=’R’
ORDER BY C.TABLE_NAME
|
|
Ultimo aggiornamento Mercoledì 11 Gennaio 2012 08:23 |
