What out what links to a given table

I needed to find out what tables referenced a given table by foreign key.  I needed to know this as I am refactoring to drop the table, and I needed to switch the keys over to use the replacement tables.   Rather than review lots of code, I decided to create a little sql script to tell me the foreign keys that are in play for the table.   Here’s the script

 

declare @TableObjectID int =  ( select object_id from sys.objects where name = 'MyTable' )

select
	parobj.name,
	parcols.name,
	refobj.name,
	refcols.name
from [sys].[foreign_key_columns] fkc
join sys.objects parobj on parobj.object_id = fkc.parent_object_id
join sys.columns parcols on parcols.object_id = fkc.parent_object_id
	and parcols.column_id = fkc.parent_column_id
join sys.objects refobj on refobj.object_id = fkc.referenced_object_id
join sys.columns refcols on refcols.object_id = fkc.referenced_object_id
	and refcols.column_id = fkc.referenced_column_id
where fkc.referenced_object_id = @TableObjectID

 

The above script will give an row for each foreign key column.  In essence, it’s all the data that I needed to know where to look to drop the existing keys, and find out the parameters on the existing keys.  This can always be extended as required.

Leave a Reply

Your email address will not be published. Required fields are marked *