zl程序教程

您现在的位置是:首页 >  后端

当前栏目

How can I list all foreign keys referencing a given table in SQL Server? 查找已知表的字段被哪些表,用来做外键关联字段

serverListSQL in 哪些 查找 Table 关联
2023-09-11 14:14:21 时间

How can I list all foreign keys referencing a given table in SQL Server?

 how to check if columns in table was used as foreign key in other tables
 

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

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.

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.

 

测试了一下,还是很给力的。直接给出一张表A,会直接找到那些用了这张表A的字段作为外键 的其他表。

注意事项,必须按照如下格式使用

EXEC sp_fkeys @pktable_name = 'CMS_Permission', @pktable_owner = 'dbo'
EXEC sp_fkeys @pktable_name = 'CMS_Resource', @pktable_owner = 'dbo'

表名不能多加东西,比如dbo.[CMS_Permission]或者[CMS_Permission],都是无法识别的表名