====== Find foreign keys referencing a table ======

Finding which table is referenced by a foreign key is easy -- just look at the FK definition. Given a table //T//, this query does the opposite -- it reports tables' FKs which reference //T//.

<code tsql>WITH cte AS (
  SELECT 
    s.name AS SchemaName
  , o.name AS TableName
  , c.name AS ColumnName
  , '[' + s.name + '].[' + o.name + '].[' + c.name + ']' AS ColumnPath
  , o.object_id AS ObjectId
  , c.column_id AS ColumnId
  FROM sys.schemas s
    INNER JOIN sys.objects o ON s.schema_id = o.schema_id
    INNER JOIN sys.columns c ON o.object_id = c.object_id
  WHERE o.type = 'U'
)
SELECT 
  fk.name
, parent.ColumnPath + ' <- ' + child.ColumnPath
FROM sys.foreign_keys fk
  INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
  INNER JOIN cte parent ON parent.ObjectId = fkc.referenced_object_id AND parent.ColumnId = fkc.referenced_column_id
  INNER JOIN cte child ON child.ObjectId = fkc.parent_object_id AND child.ColumnId = fkc.parent_column_id
WHERE parent.SchemaName LIKE '%'
AND parent.TableName = 'YourTableNameHere'
ORDER BY 
  fk.name
, parent.ColumnId
</code>

<WRAP center round tip 80%>
Note that ''child.ObjectId = fkc.parent_object_id'' is correct because ''parent_object_id'' refers to the parent of the foreign key (constraint) object rather than the parent in the FK relationship.
</WRAP>

{{tag> admin tsql foreign_key}}