How to Get List of All Foreign Keys

As part of my project to transfer our legacy database to SQL Azure I have been playing with our existing index list.  Dropping and creating new keys and index is pain in the backside as there are lots of foreign key constraints here and there.

As a quick get-around I have decided to remove all foreign keys (don’t worry! This is just for my exercise.  I will definitely put them back later, at least some of them.  This will also give me the chance to check where a key is required or if the database is missing any vital key).  I have used to following script to create a dynamic script to drop all FK constraints.

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


Thanks to Jimmy Bogards blog post about Viewing all foreign key constraints in SQL Server.

And, I think Jimmy has borrowed the idea from Pinal Dave’s excellent SQL Authority Blog.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s