Prev: Using While Loop to Union Selects?
Next: Name of procedure when running it
From: Plamen Ratchev on 12 Jun 2010 23:12
Here is a query that will match foreign keys with primary/unique keys
and pull related info:

SELECT C.TABLE_CATALOG,
C.TABLE_SCHEMA,
C.TABLE_NAME,
C.COLUMN_NAME,
X.CONSTRAINT_NAME AS FK,
X.UNIQUE_CONSTRAINT_NAME AS PK,
P.TABLE_NAME AS PK_TABLE,
P.COLUMN_NAME AS PK_COLUMN,
K.CONSTRAINT_NAME AS PK_COLUMN_CONSTRAINT
FROM INFORMATION_SCHEMA.COLUMNS AS C
JOIN INFORMATION_SCHEMA.TABLES AS T
ON C.TABLE_CATALOG = T.TABLE_CATALOG
AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN (SELECT U.TABLE_CATALOG,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME,
U.ORDINAL_POSITION,
R.CONSTRAINT_CATALOG,
R.CONSTRAINT_SCHEMA,
R.CONSTRAINT_NAME,
R.UNIQUE_CONSTRAINT_CATALOG,
R.UNIQUE_CONSTRAINT_SCHEMA,
R.UNIQUE_CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS U
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS R
ON U.CONSTRAINT_CATALOG = R.CONSTRAINT_CATALOG
AND U.CONSTRAINT_SCHEMA = R.CONSTRAINT_SCHEMA
AND U.CONSTRAINT_NAME = R.CONSTRAINT_NAME) AS X
ON C.TABLE_CATALOG = X.TABLE_CATALOG
AND C.TABLE_SCHEMA = X.TABLE_SCHEMA
AND C.TABLE_NAME = X.TABLE_NAME
AND C.COLUMN_NAME = X.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS P
ON X.UNIQUE_CONSTRAINT_CATALOG = P.CONSTRAINT_CATALOG
AND X.UNIQUE_CONSTRAINT_SCHEMA = P.CONSTRAINT_SCHEMA
AND X.UNIQUE_CONSTRAINT_NAME = P.CONSTRAINT_NAME
AND X.ORDINAL_POSITION = P.ORDINAL_POSITION
LEFT JOIN (SELECT A.CONSTRAINT_CATALOG,
A.CONSTRAINT_SCHEMA,
A.CONSTRAINT_NAME,
A.TABLE_NAME,
A.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS A
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS B
ON A.CONSTRAINT_CATALOG = B.CONSTRAINT_CATALOG
AND A.CONSTRAINT_SCHEMA = B.CONSTRAINT_SCHEMA
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND B.CONSTRAINT_TYPE = 'PRIMARY KEY') AS K
ON C.TABLE_CATALOG = K.CONSTRAINT_CATALOG
AND C.TABLE_SCHEMA = K.CONSTRAINT_SCHEMA
AND C.TABLE_NAME = K.TABLE_NAME
AND C.COLUMN_NAME = K.COLUMN_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE';

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Using While Loop to Union Selects?
Next: Name of procedure when running it