Oracle数据库中获取模式名、表名及主键存在标识的问询

问题分析 #

你的原查询因以下原因导致表名重复：

• 按constraint_type分组，同一表若有多种约束（主键、外键、检查约束）会生成多条记录
• WHERE子句逻辑错误：or ac.constraint_type != null的优先级问题，且非空判断应使用IS NOT NULL而非!= null

解决方案 #

以下两种SQL均可实现每个表唯一一行，并返回模式名、表名及主键存在状态（1=存在，0=不存在）：

方案1：使用EXISTS子查询（直观高效） #

SELECT
    ROW_NUMBER() OVER (ORDER BY at.owner, at.table_name) AS Id,
    at.owner AS Schema,
    at.table_name AS TableName,
    CASE WHEN EXISTS (
        SELECT 1
        FROM ALL_CONSTRAINTS ac
        WHERE ac.owner = at.owner
          AND ac.table_name = at.table_name
          AND ac.constraint_type = 'P'
    ) THEN 1 ELSE 0 END AS HasPrimaryKey
FROM ALL_TABLES at
WHERE at.temporary = 'N'
  AND at.owner = 'Source_schema'
  AND at.owner NOT IN ('CTXSYS', 'MDSYS', 'SYSTEM', 'XDB', 'SYS')
ORDER BY at.table_name ASC;

方案2：LEFT JOIN + 聚合函数 #

SELECT
    ROW_NUMBER() OVER (ORDER BY at.owner, at.table_name) AS Id,
    at.owner AS Schema,
    at.table_name AS TableName,
    CASE WHEN MAX(CASE WHEN ac.constraint_type = 'P' THEN 1 ELSE 0 END) = 1 THEN 1 ELSE 0 END AS HasPrimaryKey
FROM ALL_TABLES at
LEFT JOIN ALL_CONSTRAINTS ac
    ON ac.owner = at.owner
    AND ac.table_name = at.table_name
    AND ac.constraint_type = 'P'
WHERE at.temporary = 'N'
  AND at.owner = 'Source_schema'
  AND at.owner NOT IN ('CTXSYS', 'MDSYS', 'SYSTEM', 'XDB', 'SYS')
GROUP BY at.owner, at.table_name
ORDER BY at.table_name ASC;

关键说明 #

• 两种方案均按owner和table_name分组（或通过EXISTS关联），确保每个表仅返回一条记录
• HasPrimaryKey列用1/0标识主键存在状态，若需要布尔类型可替换为CASE ... THEN TRUE ELSE FALSE END（Oracle支持BOOLEAN类型，但部分客户端可能需转换为字符串）
• 原查询中at.owner in ('Source_schema')可简化为at.owner = 'Source_schema'，提升查询效率

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