如何找出哪些 FOREIGN KEY 约束引用 SQL Server 中的表? [英] 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:

消息 3726，级别 16，状态 1，第 3 行
无法删除对象dbo.UserProfile"，因为它被外键约束引用.
消息 2714，级别 16，状态 6，第 2 行
数据库中已经有一个名为UserProfile"的对象.

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.

我使用 SQL Server Management Studio 环顾四周，但找不到约束条件.如何找出外键约束?

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?

推荐答案

这里是:

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

这样，您将获得引用表和列名称.

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

根据评论建议编辑为使用 sys.tables 而不是通用的 sys.objects.谢谢，马克_s

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

这篇关于如何找出哪些 FOREIGN KEY 约束引用 SQL Server 中的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！