如何从指定模式的表列表中查询列及其对应数据

你当前的CTE能获取指定模式（xx）下的表名，但存在冗余——information_schema.columns会为每个表的每一列返回一条记录，导致同一个表名重复出现。可以先优化表名查询：

WITH table_names AS (
  SELECT DISTINCT table_name 
  FROM information_schema.tables 
  WHERE table_schema = 'xx'
)
SELECT table_name FROM table_names;

但静态SQL无法直接遍历这些表并查询数据，必须用动态SQL实现，以下是主流数据库的实现方式：

PostgreSQL 实现 #

可以编写PL/pgSQL函数，遍历目标表并输出所有数据：

CREATE OR REPLACE FUNCTION get_schema_data(p_schema text)
RETURNS SETOF record AS $$
DECLARE
  rec record;
  sql text;
BEGIN
  FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema = p_schema LOOP
    sql := format('SELECT ''%I'' AS table_name, * FROM %I.%I', rec.table_name, p_schema, rec.table_name);
    RETURN QUERY EXECUTE sql;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

-- 调用函数查询schema 'xx'下所有表的数据
SELECT * FROM get_schema_data('xx') AS (table_name text, col1 text, col2 int, ...);

注意：调用时需要手动指定返回列的结构，或者用jsonb统一格式输出，避免列结构不兼容的问题：

CREATE OR REPLACE FUNCTION get_schema_data_json(p_schema text)
RETURNS SETOF jsonb AS $$
DECLARE
  rec record;
  sql text;
BEGIN
  FOR rec IN SELECT table_name FROM information_schema.tables WHERE table_schema = p_schema LOOP
    sql := format('SELECT jsonb_build_object(''table_name'', ''%I'', ''data'', to_jsonb(t)) FROM %I.%I t', rec.table_name, p_schema, rec.table_name);
    RETURN QUERY EXECUTE sql;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM get_schema_data_json('xx');

MySQL 实现 #

通过存储过程遍历表名并执行动态查询：

DELIMITER //
CREATE PROCEDURE get_schema_data(p_schema varchar(64))
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE tb_name varchar(64);
  DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = p_schema;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO tb_name;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @sql = CONCAT('SELECT ''', tb_name, ''' AS table_name, * FROM ', p_schema, '.', tb_name);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程
CALL get_schema_data('xx');

关键说明 #

• 动态SQL的核心是根据表名拼接查询语句，注意处理表名/模式名的转义（比如PostgreSQL的%I、MySQL的反引号），避免SQL注入风险。
• 如果表结构差异大，建议用JSON格式统一输出，方便后续处理。

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