Oracle数据库查询表结构时如何追加各列对应的取值示例

不需要编写复杂存储过程，通过Oracle自带的XML动态查询能力即可在原有SQL基础上改造实现，性能开销可控。

改造后完整查询语句 #

SELECT 
    t.owner,
    t.table_name, 
    t.column_name, 
    t.data_type,
    CASE WHEN t.data_type IN ('VARCHAR', 'VARCHAR2') THEN TO_CHAR(t.char_length) ELSE '-' END AS char_length,
    x.sample_value AS 字段取值示例
FROM all_tab_cols t,
XMLTABLE(
    '/ROWSET/ROW/SAMPLE_VAL' 
    PASSING xmltype(dbms_xmlgen.getxml('SELECT /*+ FIRST_ROWS(1) */ "'||t.column_name||'" AS SAMPLE_VAL FROM "'||t.owner||'"."'||t.table_name||'" WHERE "'||t.column_name||'" IS NOT NULL FETCH FIRST 1 ROW ONLY'))
    COLUMNS sample_value VARCHAR2(4000) PATH '.'
) x
WHERE t.owner = 'DB_OWNER' -- 替换为实际要查询的Owner名称
AND t.hidden_column = 'NO' -- 过滤系统隐藏列
ORDER BY t.owner, t.table_name, t.column_id;

注意事项 #

• 性能控制：默认取每个字段的第一个非空值，不会触发全表扫描，对大表也几乎无性能压力，开销远低于存储过程逐表遍历
• 全空字段兼容：如果需要保留所有字段（包括全部值为NULL的字段），可以把关联逻辑改为LEFT JOIN XMLTABLE(...) x ON 1=1
• 采样方式调整：如果需要随机采样值，可将动态SQL中的查询逻辑改为ORDER BY DBMS_RANDOM.VALUE FETCH FIRST 1 ROW ONLY，随机采样的性能开销会略有上升
• 权限要求：执行查询的账号需要拥有目标Owner下所有表的SELECT权限，以及dbms_xmlgen系统包的执行权限
• 长字段适配：如果需要支持长度超过4000的字段返回，可将sample_value的返回类型调整为CLOB，避免内容截断
• 特殊标识符兼容：SQL中已对表名、字段名增加双引号包裹，自动支持带小写、特殊字符的表/字段名

更高性能备选方案 #

如果需要查询的表数量极多，可通过生成批量SQL的方式进一步提升性能：

1. 执行以下语句生成批量查询脚本

SELECT 'SELECT '''||table_name||''' AS table_name, '''||column_name||''' AS column_name, '''||data_type||''' AS data_type, '''||CASE WHEN data_type IN ('VARCHAR','VARCHAR2') THEN TO_CHAR(char_length) ELSE '-' END||''' AS char_length, MAX('||column_name||') AS sample_value FROM '||owner||'.'||table_name||' UNION ALL' AS gen_sql
FROM all_tab_cols
WHERE owner = 'DB_OWNER' AND hidden_column = 'NO'
ORDER BY table_name, column_id;

2. 把生成结果最后一行的UNION ALL删除后直接执行，即可拿到全量结果，性能比XML动态查询更高。

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