SQL-Quick tip #8 - Finding foreign key constraints

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

All foreign key constraints

A well designed database relates its tables to each other using foreign key relations in order to ensure data integrity. But when the database has hundreds or even thousands of tables it can be a huge challenge finding all the tables related to a specific table you may have to modify.
The query below will find all the foreign key constraints between columns with a specific column name.

DECLARE @colname VARCHAR(200) = 'customer'

SELECT fk_tab.name as [table],    
       pk_columns.name as colname,
       pk_tab.name as [fk_table],
       fk_columns.name as fk_colname
  FROM sys.foreign_keys fk
  JOIN sys.tables fk_tab on fk_tab.object_id = fk.parent_object_id
  JOIN sys.tables pk_tab on pk_tab.object_id = fk.referenced_object_id
  JOIN sys.foreign_key_columns fk_cols on fk_cols.constraint_object_id = fk.object_id
  JOIN sys.columns fk_columns ON fk_columns.object_id = fk_cols.referenced_object_id 
       AND fk_columns.column_id = fk_cols.referenced_column_id
  JOIN sys.columns pk_columns ON pk_columns.object_id = fk_cols.parent_object_id
       AND pk_columns.column_id = fk_cols.parent_column_id
 WHERE fk_columns.name LIKE '%'+ @colname +'%' OR pk_columns.name LIKE '%'+ @colname +'%'
 ORDER BY pk_tab.name

In the Northwind example below, we can see that the CustomerTypeID column of the CustomerCustomerDemo table has a foreign key relation to the primary key column CustomerTypeID of the table CustomerDemographics.