sql - 如何列出引用SQL Server中给定表的所有外键？(How can I list all foreign keys referencing a given table in SQL Server?)

Question

I need to remove a highly referenced table in a SQL Server database.

(我需要在SQL Server数据库中删除一个高度引用的表。)

How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?

(我如何获取要删除表需要删除的所有外键约束的列表？)

(SQL answers preferable over clicking about in the GUI of the management studio.)

(（与在Management Studio的GUI中单击相比，SQL的答案更好。）)

  ask by chillitom translate from so

Answer 1

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

(不知道为什么没有人建议，但是我使用sp_fkeys查询给定表的外键：)

EXEC sp_fkeys 'TableName'

You can also specify the schema:

(您还可以指定架构：)

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

(在未指定架构的情况下， 文档指出以下内容：)

If pktable_owner is not specified, the default table visibility rules of the underlying DBMS apply.

(如果未指定pktable_owner，则适用基础DBMS的默认表可见性规则。)

In SQL Server, if the current user owns a table with the specified name, that table's columns are returned.

(在SQL Server中，如果当前用户拥有具有指定名称的表，则返回该表的列。)

If pktable_owner is not specified and the current user does not own a table with the specified pktable_name, the procedure looks for a table with the specified pktable_name owned by the database owner.

(如果未指定pktable_owner并且当前用户不拥有具有指定pktable_name的表，则该过程将查找数据库所有者拥有的具有指定pktable_name的表。)

If one exists, that table's columns are returned.

(如果存在，则返回该表的列。)