Home Hacked By ScAmA Hacker #K-2@hotmail.com #pin/26895800 Schema: How do I find all the foreign keys in a database

SkypeMe

Visitatori

Schema: How do I find all the foreign keys in a database PDF Stampa E-mail
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
 
 

Linkedin

Visualizza il profilo di Lorenzo  Bossoletti su LinkedIn

ClustrMaps

Locations of visitors to this page