删除bronze.LawAggregatedPipelineSummary表时提示被外键引用却无法定位

你的自定义查询问题分析 #

• 未指定过滤目标被引用表，导致结果无法定位到bronze.LawAggregatedPipelineSummary
• 关联逻辑仅关联了主键约束和引用约束，未关联外键所在的表及列信息，无法直接识别哪些表引用了目标表
• INFORMATION_SCHEMA视图存在场景局限性，无法捕获跨数据库外键、部分特殊约束类型的信息

推荐使用sys系统视图查询（更准确全面） #

直接查询SQL Server原生系统视图，能精准定位所有引用目标表的外键：

SELECT
    fk.name AS 外键约束名,
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS 引用表架构,
    OBJECT_NAME(fk.parent_object_id) AS 引用表名,
    c.name AS 引用列名,
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS 被引用表架构,
    OBJECT_NAME(fk.referenced_object_id) AS 被引用表名,
    rc.name AS 被引用列名
FROM
    sys.foreign_keys fk
JOIN
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN
    sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN
    sys.columns rc ON fkc.referenced_object_id = rc.object_id AND fkc.referenced_column_id = rc.column_id
WHERE
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) = 'bronze'
    AND OBJECT_NAME(fk.referenced_object_id) = 'LawAggregatedPipelineSummary';

修正后的INFORMATION_SCHEMA查询 #

如果坚持使用INFORMATION_SCHEMA，需调整关联逻辑并明确过滤被引用表：

SELECT
    tc.CONSTRAINT_NAME AS 外键约束名,
    tc.TABLE_SCHEMA AS 引用表架构,
    tc.TABLE_NAME AS 引用表名,
    ccu.COLUMN_NAME AS 引用列名,
    rc.UNIQUE_CONSTRAINT_NAME AS 被引用主键约束名,
    ccu_ref.TABLE_SCHEMA AS 被引用表架构,
    ccu_ref.TABLE_NAME AS 被引用表名,
    ccu_ref.COLUMN_NAME AS 被引用列名
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON tc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
JOIN
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu_ref ON rc.UNIQUE_CONSTRAINT_NAME = ccu_ref.CONSTRAINT_NAME
WHERE
    ccu_ref.TABLE_SCHEMA = 'bronze'
    AND ccu_ref.TABLE_NAME = 'LawAggregatedPipelineSummary';

其他排查方向 #

• 跨数据库外键：如果其他数据库存在引用当前表的外键，需切换到对应数据库执行上述查询
• 全局临时表外键：检查是否有全局临时表（名称以##开头）的外键留存，可执行：

  SELECT * FROM sys.foreign_keys WHERE parent_object_id IN (SELECT object_id FROM sys.tables WHERE name LIKE '##%')
  

• 禁用的外键：禁用状态的外键仍会阻止表删除，可执行：

  SELECT name FROM sys.foreign_keys WHERE referenced_object_id = OBJECT_ID('bronze.LawAggregatedPipelineSummary') AND is_disabled = 1
  

内容的提问来源于stack exchange，提问作者WestCoastProjects