Viewing all foreign key constraints in SQL Server

This one goes in the “so I never have to look again” category.  I needed to get a list of all foreign keys in the database, for some reason which was probably dire but now escapes me.  This guy had the answer, don’t you love those MVPs?  I was going to ask it on the StackOverflow site, but then I realized I don’t want to be a complete tool.  Here’s the query:

SELECT AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id

I think from that query I created some dynamic SQL to drop and add all check constraints or some other garbage like that.  45 0 days since I opened SQL Server Management Studio.  (I had to reset the counter)

Related Articles:

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Jimmy Bogard

I'm a technical architect with Headspring in Austin, TX. I focus on DDD, distributed systems, and any other acronym-centric design/architecture/methodology. I created AutoMapper and am a co-author of the ASP.NET MVC in Action books.
This entry was posted in SQL. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Роман Федоров

    Cool! Thank you much! It’s good code!

  • Pingback: How to Get List of All Foreign Keys | ahmjt()

  • Rohit5

    Nice tip, Thanks for sharing java 6

  • Bala

    Is there any other way to get answer like this? I tried with out success. Any way thanks for your help.

    I learned a lot from Besant Technologies in my college days. They are the Best Oracle Training Institute in Chennai

  • olia

    Excellent article, here is a good article providing a way to establish which columns in a given database are joined via the primary and foreign key relationships.