I’ve spent quite a bit of time recently trying to query a Sql Server database for its foreign keys (which is easy) and what they reference, i.e. table name and field (which is not trivial). While it might be feasible to query for this type of information through multiple queries, I preferred a solution that would go easy on the database.
After multiple fruitless attempts, I gave up and googled for the solution. The solution is below. Hopefully this saves somebody else hours of frustration
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 ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id