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 f.name 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)

Dealing with parameters in expressions and strongly-typed reflection