如何快速查询Schema中缺少指定技术列的所有数据表？

针对不同数据库系统，直接查询系统元数据就能快速解决问题，以下是常见数据库的实现方式：

MySQL #

假设你要检查的技术列是create_time、update_time、operator，目标Schema为my_schema，执行以下SQL：

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'my_schema'
  AND table_type = 'BASE TABLE'
  AND table_name NOT IN (
    SELECT table_name
    FROM information_schema.columns
    WHERE table_schema = 'my_schema'
      AND column_name IN ('create_time', 'update_time', 'operator')
    GROUP BY table_name
    HAVING COUNT(DISTINCT column_name) = 3
);

逻辑说明：先筛选出Schema下的所有实体表，再排除掉包含全部指定技术列的表，剩下的就是至少缺一个技术列的表。

PostgreSQL #

方法一：基于information_schema #

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'my_schema'
  AND table_type = 'BASE TABLE'
EXCEPT
SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'my_schema'
  AND column_name IN ('create_time', 'update_time', 'operator')
GROUP BY table_name
HAVING COUNT(DISTINCT column_name) = 3;

方法二：基于PostgreSQL原生系统表 #

SELECT c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'my_schema'
  AND c.relkind = 'r' -- 仅查询实体表
  AND NOT EXISTS (
    -- 只要有一个指定列不存在，就选中该表
    SELECT 1
    FROM (VALUES ('create_time'), ('update_time'), ('operator')) AS req_cols(col)
    WHERE NOT EXISTS (
      SELECT 1
      FROM pg_attribute a
      WHERE a.attrelid = c.oid
        AND a.attname = req_cols.col
        AND NOT a.attisdropped -- 排除已标记删除的列
    )
  );

SQL Server #

SELECT t.name AS table_name
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'my_schema'
  AND NOT EXISTS (
    SELECT 1
    FROM (VALUES ('create_time'), ('update_time'), ('operator')) AS req_cols(col)
    WHERE NOT EXISTS (
      SELECT 1
      FROM sys.columns c
      WHERE c.object_id = t.object_id
        AND c.name = req_cols.col
    )
  );

通用调整说明 #

• 替换my_schema为你的目标Schema名称；
• 把('create_time', 'update_time', 'operator')中的列名换成实际需要检查的技术列；
• 如果要找完全没有任何指定技术列的表，调整HAVING条件为COUNT(DISTINCT column_name) = 0即可。

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