如何从数据库元数据查询Schema内一对一关联表及存储过程优化

原存储过程的问题分析 #

1. 逻辑判定错误：原CTE试图通过统计列同时属于主键和外键来判定一对一，但这不是一对一的核心条件——一对一的关键是外键列本身具备唯一约束（含主键），且引用目标表的主键。
2. 视图混用：同时使用INFORMATION_SCHEMA和sys系统视图，关联逻辑不严谨，容易出现匹配错误。
3. 遗漏唯一约束：没有考虑通过外键+唯一约束建立的一对一关系，只覆盖了主键相关的场景。
4. 筛选条件失效：最终的WHERE子句逻辑无法准确过滤出一对一关联，导致无关重复记录。

修正后的存储过程 #

以下是针对SQL Server的解决方案，会准确识别指定Schema中通过主键/外键、唯一约束建立的一对一关联，并将结果存入onetoone_table：

CREATE PROCEDURE GetOneToOneRelationships
    @schemaName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- 清理目标表（如果存在），避免重复数据
    IF OBJECT_ID('onetoone_table', 'U') IS NOT NULL
        DROP TABLE onetoone_table;

    -- 核心查询：筛选一对一关联
    SELECT
        parent_table.name AS table_name,
        parent_col.name AS column_name,
        referenced_table.name AS referenced_table_name,
        referenced_col.name AS referenced_column_name
    INTO onetoone_table
    FROM sys.foreign_key_columns fk_cols
    -- 关联外键所在的父表和列
    INNER JOIN sys.tables parent_table 
        ON fk_cols.parent_object_id = parent_table.object_id
        AND parent_table.schema_id = SCHEMA_ID(@schemaName)
    INNER JOIN sys.columns parent_col 
        ON fk_cols.parent_object_id = parent_col.object_id
        AND fk_cols.parent_column_id = parent_col.column_id
    -- 关联被引用的子表和列
    INNER JOIN sys.tables referenced_table 
        ON fk_cols.referenced_object_id = referenced_table.object_id
    INNER JOIN sys.columns referenced_col 
        ON fk_cols.referenced_object_id = referenced_col.object_id
        AND fk_cols.referenced_column_id = referenced_col.column_id
    -- 关联外键约束
    INNER JOIN sys.foreign_keys fk 
        ON fk_cols.constraint_object_id = fk.object_id
    -- 检查：被引用的列是目标表的主键
    INNER JOIN sys.key_constraints referenced_pk 
        ON referenced_table.object_id = referenced_pk.parent_object_id
        AND referenced_pk.type = 'PK'
        AND referenced_col.column_id IN (
            SELECT column_id FROM sys.index_columns 
            WHERE object_id = referenced_pk.parent_object_id 
            AND index_id = referenced_pk.unique_index_id
        )
    -- 检查：外键列所在的父列有唯一约束（包括主键）
    WHERE EXISTS (
        SELECT 1
        FROM sys.key_constraints uk
        INNER JOIN sys.index_columns ic 
            ON uk.parent_object_id = ic.object_id 
            AND uk.unique_index_id = ic.index_id
        WHERE uk.parent_object_id = parent_table.object_id
            AND uk.type IN ('PK', 'U') -- PK是主键自带唯一，U是唯一约束
            AND ic.column_id = parent_col.column_id
    )
    -- 去重：避免同一关联被多次统计
    GROUP BY
        parent_table.name, parent_col.name, referenced_table.name, referenced_col.name;
END

关键判定逻辑说明 #

• 被引用列必须是目标表的主键：确保被引用的记录是唯一的，这是一对一关系的基础。
• 外键列必须具备唯一约束（含主键）：保证父表中每条记录只能对应子表的一条记录，避免一对多。
• 限定Schema范围：通过SCHEMA_ID(@schemaName)精准过滤指定Schema下的表。
• 去重处理：通过GROUP BY避免同一关联因索引列等原因重复输出。

使用方式 #

执行存储过程时传入Schema名称即可：

EXEC GetOneToOneRelationships @schemaName = 'YourSchemaName';

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