SQL Server: Show all the FOREIGN KEYS that reference a TABLE

For SQL 2005+


DECLARE
     @tableName nvarchar(MAX)
SET
    @tableName = 'tableName' -- (1)

SELECT DISTINCT
     ccu.table_name,
     ccu.constraint_name
FROM
     INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN
     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
          ON rc.unique_constraint_name = tc.constraint_name
INNER JOIN
     INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
          ON ccu.constraint_name = rc.constraint_name
WHERE
     tc.table_name = @tableName

Notes:

  • Change line (1) to specify the table 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: