Find Foreign Keys For Table

Here’s a query to find all foreign keys for a table, both keys that reference the specified table, and keys where the table is the child. In SSMS, you can only see the keys for a table where the table is on the child end.

declare @TableName nvarchar(128) = 'Person'

select t.name as TableName, rt.name as ReferencedTableName, 
	f.name as ForeignKeyName, f.is_disabled, f.is_not_trusted
from sys.foreign_keys as f
join sys.tables as t
	on t.object_id = f.parent_object_id
join sys.tables as rt
	on rt.object_id = f.referenced_object_id
where t.name = @TableName
	or rt.name = @TableName
order by t.name, rt.name, f.name
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: