Find a foreign key by searching for the table or column from either end

This is helpful for finding not only "what does this foreign key refer to?" but also "are there any foreign keys out there that refer to this table?"

Things you might want to customise:

• which column to sort by?
• do you want to compare to just the fk table, the pk table, the fk column, the pk column?

I've tried to make it super-easy to comment those in or out!

Declare @searchTerm nvarchar(100) = 'Person'								-- SEARCH TERM
Select
	schema_name(tab.schema_id) + '.' + tab.name as [fk_table],
	col.name as fk_column,
	case when fk.object_id is not null then '>-' else null end as rel,
	schema_name(pk_tab.schema_id) + '.' + pk_tab.name as [pk_table],
	pk_col.name as pk_column,
	fk_cols.constraint_column_id as Cons_Col_no,
	fk.name as fk_constraint_name
from
	sys.tables tab
inner join sys.columns col
		on col.object_id = tab.object_id
left outer join sys.foreign_key_columns fk_cols
		on fk_cols.parent_object_id = tab.object_id
		and fk_cols.parent_column_id = col.column_id
left outer join sys.foreign_keys fk
		on fk.object_id = fk_cols.constraint_object_id
left outer join sys.tables pk_tab
		on pk_tab.object_id = fk_cols.referenced_object_id
left outer join sys.columns pk_col
		on pk_col.column_id = fk_cols.referenced_column_id
		and pk_col.object_id = fk_cols.referenced_object_id
where
	tab.name not like '__Migra%' -- Exclude whatever you want...
and not fk.object_id is null
and (
	schema_name(tab.schema_id) + '.' + tab.name like				-- fk_table
	'%' + @searchTerm + '%'
or
	col.name like																						-- fk_column
	'%' + @searchTerm + '%'
or
	schema_name(pk_tab.schema_id) + '.' + pk_tab.name like	-- pk_table
	'%' + @searchTerm + '%'
or
	pk_col.name like																				-- pk_column,
	'%' + @searchTerm + '%'
)
order by
-- 1																											-- fk_table
-- 2																											-- fk_column
-- 4																											-- pk_table
-- 5																											-- pk_column
4