DEV Community

Cover image for SQL-Quick tip #8 - Finding foreign key constraints
Allan Simonsen
Allan Simonsen

Posted on

3 2

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
Enter fullscreen mode Exit fullscreen mode

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.

Sql Server Management Studio screenshot

Hostinger image

Get n8n VPS hosting 3x cheaper than a cloud solution

Get fast, easy, secure n8n VPS hosting from $4.99/mo at Hostinger. Automate any workflow using a pre-installed n8n application and no-code customization.

Start now

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay