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

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]，都是无法识别的表名

 

作者:Chuck Lu    GitHub