如何列出引用 SQL Server 中给定表的所有外键? [英] How can I list all foreign keys referencing a given table in SQL Server?

问题描述

我需要删除 SQL Server 数据库中高度引用的表.如何获取删除表时需要删除的所有外键约束的列表?

I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table?

(SQL 答案优于在管理工作室的 GUI 中单击.)

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

推荐答案

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

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

EXEC sp_fkeys 'TableName'

您还可以指定架构:

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

不指定架构，文档 声明如下:

Without specifying the schema, the docs state the following:

如果不指定pktable_owner，则默认表可见性规则应用底层 DBMS.

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

在 SQL Server 中，如果当前用户拥有一个指定的表name，返回该表的列.如果 pktable_owner 不是指定并且当前用户不拥有具有指定的表pktable_name，该过程查找具有指定的表数据库所有者拥有的 pktable_name.如果存在，则该表的返回列.

In SQL Server, if the current user owns a table with the specified name, that table's columns are returned. 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. If one exists, that table's columns are returned.

这篇关于如何列出引用 SQL Server 中给定表的所有外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！