请求编写SQL查询识别Books表字段的跨表引用与使用情况

需要查询Books表（字段：id、author_id、genre_id、title、page）的字段被其他表引用/使用的情况，返回结果包含field_name、used_on、referenced_from三个列，期望结果如下：

结果说明 #

• id字段被transaction_details和revision_details表使用（这两个表通过book_id关联Books表的id）
• author_id字段未被其他表使用，但引用自authors表
• genre_id字段未被其他表使用，但引用自genres表
• title和page字段无跨表关联，对应值为null

当前尝试的SQL #

DECLARE @TABLENAME AS VARCHAR(20)
SET @TABLENAME = 'books'   

SELECT 
    c.name AS field_name,
    CASE WHEN used_on_table.name = @TABLENAME THEN NULL ELSE used_on_table.name END AS used_on,
    CASE WHEN referenced_from_table.name = @TABLENAME THEN NULL ELSE referenced_from_table.name END AS referenced_from
FROM 
    sys.columns c
LEFT JOIN sys.foreign_key_columns fkc ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT JOIN sys.tables used_on_table ON c.object_id = used_on_table.object_id
LEFT JOIN sys.tables referenced_from_table ON fkc.referenced_object_id = referenced_from_table.object_id
WHERE 
    OBJECT_NAME(c.object_id) = @TABLENAME;

存在的问题 #

1. 出现同一外部表重复的情况
2. 部分字段的used_on和referenced_from为同一表，逻辑错误
3. 结果中包含Books表自身，不符合需求
4. 单个字段的used_on与referenced_from不应为同一表
5. 理想状态下，这两个字段应为null或不同表
6. 未支持单个字段对应多个used_on的场景

当前错误结果 #

测试表结构与数据 #

CREATE TABLE authors (
    id INT PRIMARY KEY
);

CREATE TABLE genres (
    id INT PRIMARY KEY
);

CREATE TABLE books (
    id INT PRIMARY KEY,
    author_id INT,
    genre_id INT,
    title VARCHAR(255),
    page INT,
    FOREIGN KEY (author_id) REFERENCES authors(id),
    FOREIGN KEY (genre_id) REFERENCES genres(id)
);

CREATE TABLE transaction_details (
    transaction_id INT PRIMARY KEY,
    book_id INT,
    FOREIGN KEY (book_id) REFERENCES books(id)
);

CREATE TABLE revision_details (
    id INT PRIMARY KEY,
    book_id INT,
    FOREIGN KEY (book_id) REFERENCES books(id)
);


INSERT INTO authors VALUES (101);
INSERT INTO genres VALUES (201);
INSERT INTO books VALUES (1, 101, 201, 'Book Title 1', 150);
INSERT INTO transaction_details VALUES (1, 1);
INSERT INTO revision_details VALUES (1, 1);

修正后的SQL查询 #

要区分两种关联场景：当前表字段被其他表作为外键引用（used_on） 和 当前表字段作为外键引用其他表（referenced_from），需要分别查询这两种情况再合并结果：

DECLARE @TABLENAME AS VARCHAR(20)
SET @TABLENAME = 'books'

-- 查询字段被其他表引用的情况（used_on）
WITH used_on_cte AS (
    SELECT
        c.name AS field_name,
        OBJECT_NAME(fkc.parent_object_id) AS used_on,
        NULL AS referenced_from
    FROM sys.columns c
    JOIN sys.foreign_key_columns fkc 
        ON c.object_id = fkc.referenced_object_id 
        AND c.column_id = fkc.referenced_column_id
    WHERE OBJECT_NAME(c.object_id) = @TABLENAME
),
-- 查询字段引用其他表的情况（referenced_from）
referenced_from_cte AS (
    SELECT
        c.name AS field_name,
        NULL AS used_on,
        OBJECT_NAME(fkc.referenced_object_id) AS referenced_from
    FROM sys.columns c
    JOIN sys.foreign_key_columns fkc 
        ON c.object_id = fkc.parent_object_id 
        AND c.column_id = fkc.parent_column_id
    WHERE OBJECT_NAME(c.object_id) = @TABLENAME
),
-- 收集所有字段，确保无关联的字段也能显示
all_fields_cte AS (
    SELECT name AS field_name FROM sys.columns 
    WHERE OBJECT_NAME(object_id) = @TABLENAME
)
-- 合并所有结果，处理重复和空值
SELECT
    af.field_name,
    uo.used_on,
    rf.referenced_from
FROM all_fields_cte af
LEFT JOIN used_on_cte uo ON af.field_name = uo.field_name
LEFT JOIN referenced_from_cte rf ON af.field_name = rf.field_name
-- 处理一个字段对应多个used_on的情况，确保每条记录对应一个关联表
UNION ALL
SELECT
    af.field_name,
    uo.used_on,
    NULL AS referenced_from
FROM all_fields_cte af
JOIN used_on_cte uo ON af.field_name = uo.field_name
WHERE af.field_name IN (SELECT field_name FROM used_on_cte GROUP BY field_name HAVING COUNT(*) > 1)
-- 去重并排序
ORDER BY af.field_name, uo.used_on;

修正说明 #

1. 用CTE分别拆分两种关联逻辑：
   • used_on_cte：查询哪些表把当前表的字段作为外键（即当前字段被其他表使用）
   • referenced_from_cte：查询当前表的字段作为外键引用了哪些表
2. all_fields_cte确保无任何关联的字段（title、page）也能出现在结果中
3. 使用UNION ALL处理单个字段对应多个used_on的场景，保证每个关联表单独成一行
4. 彻底分离used_on和referenced_from的逻辑，避免同一字段同时出现两种关联的混淆

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