Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 1 down vote accepted

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.