Little SQL Server Tricks: Find All Foreign Keys Pointing to Your Table

Foreign keys are a fundamental feature of relational databases. They are the little helpers that ensure the integrity of your data. Even better, they help you with the documentation of your data. All you need to do is open the table definition and you see the foreign keys to the tables that this one depends on.

The other way around is a lot harder, especially when you can’t use the newest SQL Server Management Studio. However, there is a little function in SQL Server (going back to 2008) that will show you which other tables depend on your table. All you need to do is to use the sp_fkeys procedure:

1
EXEC sp_fkeys 'Table', 'Schema'

If I run this command against the AdventureWorks database for the Person.Address table, I get this result:

1
EXEC sp_fkeys 'Address', 'Person'

result of EXEC sp_fkeys

The most interesting columns are these four:

  • FKTABLE_OWNER: the schema in which the table is located
  • FKTABLE_NAME: the name of the table that points to your table
  • FKCOLUMN_NAME: the column name in which your table is referenced
  • FK_NAME: the name of the foreign key that points to your table

That should be all you need to find all tables that have a foreign key relation with the table in question.