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.
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(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.
Other posts by DustinRyan