Oracle如何批量查询指定前缀表的字段等详细元数据信息

核心实现SQL #

直接在SQL Developer中执行以下脚本，即可一次性获取TEST schema下表名以A/B/C开头的所有表的指定维度字段信息，无需逐表查询：

SELECT
    t.table_name AS "Table name",
    c.column_name AS "Column name",
    c.data_type AS "Column datatype",
    c.data_length AS "Column length",
    c.data_default AS "Column default value",
    c.nullable AS "Column allow null",
    com.comments AS "Column comment"
FROM
    all_tables t
INNER JOIN all_tab_columns c 
    ON t.owner = c.owner 
    AND t.table_name = c.table_name
LEFT JOIN all_col_comments com 
    ON c.owner = com.owner 
    AND c.table_name = com.table_name 
    AND c.column_name = com.column_name
WHERE
    t.owner = 'TEST'
    -- 表名前缀匹配规则，可按需修改
    AND REGEXP_LIKE(t.table_name, '^[ABC]')
ORDER BY
    t.table_name, 
    c.column_id;

自定义调整说明 #

• 表名匹配规则修改：如果需要调整匹配的表名前缀，直接修改REGEXP_LIKE函数内的正则规则即可。例如需要匹配D/E/F开头的表，将规则改为^[DEF]；需要匹配所有前缀为TMP_的表，将规则改为^TMP_。
• 长度字段适配：上述SQL中c.data_length返回的是字段字节长度，如果需要获取按字符计数的字段长度（适配多字节字符集、NCHAR/NVARCHAR2类型场景），将该字段替换为c.char_length即可。
• 字段值含义：Column allow null字段返回值为Y时代表字段允许为空，返回N时代表字段非空。
• 权限问题处理：如果执行脚本提示无权限，可直接使用TEST账号登录，将脚本中所有视图前缀从all_替换为user_，同时删除t.owner = 'TEST'这行过滤条件即可正常运行。

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