Viewing all foreign key constraints in SQL Server

27 November, 2008. It was a Thursday.

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

Visualizing LINQ expressions in the debugger →