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.
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.