To properly locating the primary key - foreign key relations in a database sounds like a tough task. Mohit Nayyar posted a technique to find out primary key and foreign key that have the same column names. The limitation of that is that, in reality, column names are not always the same in a database. For example, in the AdvantureWorks sample database in SQL2005, the SalesPersonID in Sales.SalesPerson table has a foreign key reference to the EmployeeID column in the HumanResources.Employee table.
This script will map the primary key and foreign key, regardless if the column names are same or not. This script has been tested on both SQL2000 and 2005. This is another evidence of the usefulness of the information_schema view.
SELECT
'FK_Table_Schema' = ccu1.table_schema
,'FK_Table_Name' = ccu1.table_name
,'FK_Column_Name' = ccu1.column_name
,'FK_Constraint_Name' = rc.constraint_name
,'PK_Table_Schema' = ccu.table_schema
,'PK_Table_Name' = ccu.table_name
,'PK_Column_Name' = ccu.column_name
,'PK_Constraint_Name' = ccu.constraint_name
FROM
information_schema.constraint_column_usage CCU
INNER JOIN
information_schema.referential_constraints RC
ON
CCU.constraint_name = RC.unique_constraint_name
INNER JOIN
information_schema.constraint_column_usage CCU1
ON
RC.constraint_name = ccu1.constraint_name
WHERE
ccu.constraint_name NOT IN (
SELECT
constraint_name
FROM
information_schema.referential_constraints
)
ORDER BY
FK_Table_Schema
,FK_Table_Name
,PK_Table_Schema
,PK_Table_Name
If you are interest in a particular column, simple add " and ccu.column_name=’<column name>’" in the where cluse.
Mohit’s Column: http://www.sqlservercentral.com/scripts/Miscellaneous/31983/