Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

TSQL Script to Find Foreign Key References to a Given Column

  • 8 July 2015
  • Author: DustinRyan
  • Number of views: 12881
  • 0 Comments

It’s always kind of a pain to have to hunt down all those foreign key references so you can address the issues. So I put this script together (based on a script found on StackOverflow) in order to help me find all the required information related to a particular column in a specified table. I’m mostly posting this for my own reference later and for anyone else that may find this useful, so enjoy!

SELECT OBJECT_NAME(f.object_id) as ForeignKeyConstraintName,
    OBJECT_NAME(f.parent_object_id) TableName,
    COL_NAME(fk.parent_object_id,fk.parent_column_id) ColumnName,
    OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName,
    COL_NAME(fk.referenced_object_id,fk.referenced_column_id) as ReferencedColumnName

FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fk 
        ON f.OBJECT_ID = fk.constraint_object_id
    INNER JOIN sys.tables t
        ON fk.referenced_object_id = t.object_id

WHERE OBJECT_NAME(fk.referenced_object_id) = 'your table name'
    and COL_NAME(fk.referenced_object_id,fk.referenced_column_id) = 'your key column name'

 

Here’s a picture of what the results look like. I ran this query against the ReportServer database used for SSRS in case you were wondering.

T-SQL to find FK key columns

If you want to find every Foreign Key in your database, just eliminate the Where clause to bring back all the FKs. Hopefully you found this as useful as I did.


Print
Categories: SQL Server
Tags:
Rate this article:
No rating
DustinRyan

DustinRyanDustinRyan

Other posts by DustinRyan

Please login or register to post comments.