Checking Foreign Key References

You may come across a situation where you need to check which tables are connected to a particular via a foreign key referential constraint. You may need this information when you are trying to delete one or more records or truncate a table.

Below is a script that you can use to check all the tables that are related to the table in question. In this example I am checking for the table "Employees"

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS RefTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS RefColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME (f.referenced_object_id)='Employees'

Below is a result showing the three tables it is connected with.

ForeignKey	TableName	ColumnName	RefTableName	RefColumnName
FK_Emp_Addresses	EmpAddresses	EmployeeID	Employees	EmployeeID
FK_Emp_Phones	EmpPhones	EmployeeID	Employees	EmployeeID
FK_Emp_Attributes	EmpAttributes	EmployeeID	Employees	EmployeeID

Thank you.