Finding References (Foreign Keys) To This Table

Occasionally I’ve faced with the task to change the value of the primary key, say, in Employee table in SQL Server database. Everything would be OK if this table is not referenced from the other tables. So when I change the value in primary key EmployeeId column, I would break the foreign key constraints.

When you create a table you can specify a REFERENCES … ON UPDATE CASCADE clause, so all changes in primary key would be propagated to the underlying tables (look here for CREATE TABLE syntax and here for referential integrity constraints). But this is not my case, I have the default NO ACTION option. I want to know all tables and columns that are referencing my primary key. This can be done by one of the three ways:

  1. stored procedure

    UPDATE 2016-02-01:
    If a table is not in a dbo schema, add a second parameter for owner.
  2. using system views from sys schema
  3. using INFORMATION_SCHEMA views

These methods work perfectly when you’ve set foreign keys. There may be a situation when you have a logical reference, but it’s not implemented as a constraint. You can try to search for a column name. Maybe you will be lucky if you have strong naming conventions and you include PK column name in FK column name, i.e. primary key is EmployeeId, so the foreign key will be BossEmployeeId or ManagerEmployeeId. If you don’t follow this rule, you might have simple name like ManagerId and it will be more harder to guess where the reference might be.

This query could help you to find tables by column name.

UPDATE 2016-04-13:
Modified version of the above query that include schema name.

Leave a Reply

Your email address will not be published. Required fields are marked *