如何在 SQLServer 中查找外键依赖项？

如何找到特定列上的所有外键依赖项？

有哪些不同的替代方案(图形化的 SSMS、 SQLServer 中的查询/视图、第三方数据库工具、 .NET 中的代码) ？

195381 次浏览
小开

我非常喜欢使用的一种方法是 红门软件的 SQL 依赖跟踪器。您可以放入任何数据库对象，如表、存储过程等，然后它将自动在所有其他依赖于您所选项的对象之间绘制关系线。

提供了模式中依赖项的非常好的图形表示。

小开
最佳答案

下面的查询将帮助您入门。它列出了当前数据库中的所有外键关系。

SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME

还可以在 SQLServer 管理工作室的数据库关系图中以图形方式查看关系。

小开

试试: sp_help [table_name]

您将获得有关表的所有信息，包括所有外键

小开

我认为这个剧本比较便宜:

SELECT f.name AS ForeignKey, OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
小开

使用信息 _ 模式;

SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM KEY_COLUMN_USAGE
WHERE (table_name = *tablename*) AND NOT (REFERENCED_TABLE_NAME IS NULL)
小开

如果计划删除或重命名仅查找外键依赖项的表或列，可能还不够。

引用未与外键连接的表 -您还需要搜索引用未与外键连接的表(我见过许多设计糟糕的数据库，它们没有定义外键，但确实有相关数据)。解决方案可能是在所有表中搜索列名并查找类似的列。

其他数据库对象 -这可能有点偏离主题，但是如果您正在查找所有引用，那么检查依赖对象也很重要。

GUI Tools-尝试 SSMS 的“查找相关对象”选项或工具，如 ApexSQL 搜索(免费工具，集成到 SSMS) ，以识别所有相关对象，包括与外键连接的表。

小开

非常感谢约翰 · 桑瑟姆，他的问题是了不起的！

另外: 应该在查询的末尾添加“ AND PT.ORDINAL _ POSITION = CU.ORDINAL _ POSITION”。

如果在主键中有多个字段，这个语句将匹配相应的字段(我有过这种情况，您的查询创建了所有组合，因此对于主键中的2个字段，对应的外键有4个结果)。

(对不起，我不能评论约翰的回答，因为我没有足够的声誉点)。

小开

此查询将返回表中外键的详细信息，它支持多列键。

    SELECT *
FROM
(
SELECT
T1.constraint_name ConstraintName,
T2.COLUMN_NAME ColumnName,
T3.TABLE_NAME RefTableName,
T3.COLUMN_NAME RefColumnName,
T1.MATCH_OPTION MatchOption,
T1.UPDATE_RULE UpdateRule,
T1.DELETE_RULE DeleteRule
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS T1
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE T2
ON T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE T3
ON T1.UNIQUE_CONSTRAINT_NAME = T3.CONSTRAINT_NAME
AND T2.ORDINAL_POSITION = T3.ORDINAL_POSITION) A
WHERE A.ConstraintName = 'table_name'
小开

因为您的问题是针对单个表的，所以您可以使用以下方法:

EXEC sp_fkeys 'TableName'

我在这里找到的:

Https://stackoverflow.com/a/12956348/652519

我很快就找到了我需要的信息。它列出了外键的表、列和名称。

剪辑

这里有一个文档链接，详细介绍了可以使用的不同参数: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fkeys-transact-sql

小开

只是一张“约翰 · 桑森”回复的便条,

如果寻求 外键的依赖性，我认为 PT Where 条款应该是:

i1.CONSTRAINT_TYPE = 'FOREIGN KEY'  -- instead of 'PRIMARY KEY'

及其 开始条件:

ON PT.TABLE_NAME = FK.TABLE_NAME – instead of PK.TABLE_NAME

通常使用外表的主键，我认为这个问题以前没有被注意到。

小开

经过长时间的寻找，我找到了一个可行的解决方案。 我的数据库不使用 sys.foreign _ key _ column，information _ schema. key _ column _ using 只包含主键。

我使用 SQLServer2015

解决方案1(很少使用)

如果其他解决方案不起作用，这种方法也会起作用:

        WITH CTE AS
(
SELECT
TAB.schema_id,
TAB.name,
COL.name AS COLNAME,
COl.is_identity
FROM
sys.tables TAB INNER JOIN sys.columns COL
ON TAB.object_id = COL.object_id
)
SELECT
DB_NAME() AS [Database],
SCHEMA_NAME(Child.schema_id) AS 'Schema',
Child.name AS 'ChildTable',
Child.COLNAME AS 'ChildColumn',
Parent.name AS 'ParentTable',
Parent.COLNAME AS 'ParentColumn'
FROM
cte Child INNER JOIN CTE Parent
ON
Child.COLNAME=Parent.COLNAME AND
Child.name<>Parent.name AND
Child.is_identity+1=Parent.is_identity

解决方案2(常用)

在大多数情况下，这种方法都可以很好地工作:

        SELECT
DB_NAME() AS [Database],
SCHEMA_NAME(fk.schema_id) AS 'Schema',
fk.name 'Name',
tp.name 'ParentTable',
cp.name 'ParentColumn',
cp.column_id,
tr.name 'ChildTable',
cr.name 'ChildColumn',
cr.column_id
FROM
sys.foreign_keys fk
INNER JOIN
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN
sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
WHERE
-- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tp.name, '.', cp.name) LIKE '%my_table_name%' OR
-- CONCAT(SCHEMA_NAME(fk.schema_id), '.', tr.name, '.', cr.name) LIKE '%my_table_name%'
ORDER BY
tp.name, cp.column_id
小开

可以使用 INFORMATION _ SCHEMA。KEY _ COLUMN _ USAGE 和 sys.foreign _ KEY _ column，以获取表的外键元数据，例如: Constraint name、 Reference table 和 Reference column 等。

下面是查询:

SELECT  CONSTRAINT_NAME, COLUMN_NAME, ParentTableName, RefTableName,RefColName FROM
(SELECT CONSTRAINT_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '<tableName>') constraint_details
INNER JOIN 
(SELECT ParentTableName, RefTableName,name ,COL_NAME(fc.referenced_object_id,fc.referenced_column_id) RefColName  FROM (SELECT object_name(parent_object_id) ParentTableName,object_name(referenced_object_id) RefTableName,name,OBJECT_ID  FROM sys.foreign_keys WHERE parent_object_id = object_id('<tableName>') ) f
INNER JOIN 
sys.foreign_key_columns AS fc  ON  f.OBJECT_ID = fc.constraint_object_id ) foreign_key_detail
on foreign_key_detail.name = constraint_details.CONSTRAINT_NAME
小开 
SELECT  obj.name AS FK_NAME,
sch.name AS [schema_name],
tab1.name AS [table],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

它会给你:

FK 本身 FK 所属的模式

  • “引用表”或具有 FK 的表
  • “引用列”或引用表中指向 FK 的列
  • “被引用的表”或具有您的 FK 所指向的键列的表
  • “引用的列”或者是你的 FK 指向的键

提交答案