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