如何查询Oracle所有表字段、数据类型、约束及对应的引用表和引用列

你原有SQL缺少外键对应的引用约束关联逻辑，Oracle的外键关联信息存储在all_constraints表的r_constraint_name字段，该字段存储了外键对应的主键/唯一键约束名，通过该字段二次关联约束表和约束列表即可拿到引用表、引用列信息。

优化后的查询SQL如下：

SELECT
    at.owner,
    at.table_name,
    atc.column_name AS column_names,
    atc.data_type,
    ac.constraint_type,
    -- 仅外键约束返回引用表信息，其余约束返回空
    CASE WHEN ac.constraint_type = 'R' THEN ac_r.table_name ELSE NULL END AS reference_table,
    -- 仅外键约束返回引用列信息，其余约束返回空
    CASE WHEN ac.constraint_type = 'R' THEN acc_r.column_name ELSE NULL END AS reference_column
FROM
    all_tables at
    JOIN all_tab_columns atc 
        ON at.table_name = atc.table_name 
        AND at.owner = atc.owner
    LEFT JOIN all_cons_columns acc 
        ON atc.column_name = acc.column_name
        AND acc.table_name = at.table_name
        AND acc.owner = at.owner
    LEFT JOIN all_constraints ac 
        ON ac.constraint_name = acc.constraint_name
        AND ac.owner = acc.owner
    -- 关联外键对应的主键/唯一键约束
    LEFT JOIN all_constraints ac_r
        ON ac.r_constraint_name = ac_r.constraint_name
        AND ac.r_owner = ac_r.owner
    -- 关联主键/唯一键约束对应的字段信息
    LEFT JOIN all_cons_columns acc_r
        ON ac_r.constraint_name = acc_r.constraint_name
        AND ac_r.owner = acc_r.owner
        AND acc_r.position = acc.position
WHERE
    at.owner NOT IN ( 'SYS', 'SYSTEM', 'DBSNMP', 'DVSYS', 'GSMADMIN_INTERNAL',
                      'LBACSYS',
                      'MDSYS',
                      'OJVMSYS',
                      'ORDDATA',
                      'WMSYS',
                      'XDB',
                      'CTXSYS',
                      'APPQOSSYS',
                      'AUDSYS',
                      'OUTLN',
                      'DBSFWUSER',
                      'ORDSYS',
                      'OLAPSYS' )
GROUP BY
    at.owner,
    at.table_name,
    atc.column_name,
    atc.data_type,
    ac.constraint_type,
    ac_r.table_name,
    acc_r.column_name
ORDER BY
    at.owner,
    at.table_name,
    atc.column_name;

相关说明：

• 所有关联条件都新增了owner字段匹配，避免不同schema下同名表、同名约束关联错误，这是原有SQL的核心问题之一
• 新增acc_r.position = acc.position关联条件，完美适配联合外键场景，保证多个外键字段和引用表的主键字段顺序一一对应
• 非外键类型的约束（主键P、唯一键U、检查约束C等）的引用表、引用列返回空，符合实际业务逻辑
• 如果仅需要查询外键约束相关信息，可以新增WHERE ac.constraint_type = 'R'过滤条件，或者将对应左连接改为内连接

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