Need to figure out what FKs point to a specific table

Question

In SQLServer 2008 r2. I need to figure for a specific table what FK's in other tables point to it.

I try:

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

But this does not give me what I need.

Any tips?

Answer 1

You want to use the management views:

sys.foreign_keys

and

sys.foreign_key_columns

The latter one has column and object IDs for both sides of the constraint, so you can do a query like:

SELECT OBJECT_NAME(Constraint_object_id) as 'ConstraintTable',
       c1.name,
       OBJECT_NAME(Parent_object_id) 'ParentTable',
       c2.name
FROM Sys.foreign_key_columns fk
INNER JOIN sys.columns c1
  ON c1.column_id = fk.constraint_column_id
INNER JOIN sys.columns c2
  ON c2.column_id = fk.parent_column_id

Answer 2

This looks like it may get you what you need:

http://www.sqlskills.com/BLOGS/PAUL/post/Programmatically-picking-apart-foreign-key-constraints.aspx

Answer 3

This link is to a database reverse engineering script that I wrote a few years ago. Amongst other things it has a query that reverse engineers foreign keys. You may be able to get what you want by adapting it.