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 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

 

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.

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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