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.
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.
It’s a very helpful script. Thank you for posting it.
You’re welcome!