如何找到所有的外键？ [英] How to find all foreign keys?

问题描述

我想在我的数据库中找到所有的引用表有一个外键指向一个特定的引用表。是否有一个查询，我可以跑这样做？

不知道这个问题是否令人困惑。让我知道如果是这样，我可以尝试更详细地解释它。

解决方案

以下查询或修改tehreof将做 - 在Sql服务器
您也可以提供目录和模式信息

pre $ select $ tab $ TABLE_TABLE_NAME
INFORMATION_SCHEMA .REFERENTIAL_CONSTRAINTS作为内部联接
INFORMATION_SCHEMA.TABLE_CONSTRAINTS作为原始
在ref.UNIQUE_CONSTRAINT_NAME = prim.CONSTRAINT_NAME
和ref.UNIQUE_CONSTRAINT_CATALOG = prim.CONSTRAINT_CATALOG
和ref.UNIQUE_CONSTRAINT_SCHEMA = prim。 CONSTRAINT_SCHEMA
- 和（'PRIMARY KEY'，'UNIQUE'）中的prim.CONSTRAINT_TYPE
内连接
上的INFORMATION_SCHEMA.TABLE_CONSTRAINTS tab1 ref.CONSTRAINT_NAME = tab1.CONSTRAINT_NAME
和ref .CONSTRAINT_CATALOG = tab1.CONSTRAINT_CATALOG
和ref.CONSTRAINT_SCHEMA = tab1.CONSTRAINT_SCHEMA
其中，prim.TABLE_NAME ='YourTablename'

I'd like to find all referencing tables in my db that have a foreign key that points to a specific referenced table. Is there a query that I can run to do this?

Not sure if the question is confusing. Let me know if it is and I can try to explain it in more detail.

解决方案

The following query or Modification tehreof will do - in Sql server You can also supply catalog and schema info

select tab1.TABLE_NAME from 
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as ref inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as prim
on ref.UNIQUE_CONSTRAINT_NAME=prim.CONSTRAINT_NAME
and ref.UNIQUE_CONSTRAINT_CATALOG=prim.CONSTRAINT_CATALOG
and ref.UNIQUE_CONSTRAINT_SCHEMA=prim.CONSTRAINT_SCHEMA
--and prim.CONSTRAINT_TYPE in('PRIMARY KEY','UNIQUE')
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tab1 on
ref.CONSTRAINT_NAME=tab1.CONSTRAINT_NAME
and ref.CONSTRAINT_CATALOG=tab1.CONSTRAINT_CATALOG
and ref.CONSTRAINT_SCHEMA=tab1.CONSTRAINT_SCHEMA
where prim.TABLE_NAME='YourTablename'

这篇关于如何找到所有的外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！