Melina Melina - 1 year ago 114
SQL Question

How can I find out what FOREIGN KEY constraint references a table in SQL Server?

I am trying to drop a table but getting the following message:

Msg 3726, Level 16, State 1, Line 3

Could not drop object 'dbo.UserProfile' because it is referenced by a FOREIGN KEY constraint.

Msg 2714, Level 16, State 6, Line 2

There is already an object named 'UserProfile' in the database.

I looked around with SQL Server Management Studio but I am unable to find the constraint. How can I find out the foreign key constraints?

Answer Source

Here it is:

   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
   sys.foreign_keys AS f
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

This way, you'll get the referencing table and column name.

Edited to use sys.tables instead of generic sys.objects as per comment suggestion. Thanks, marc_s

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download