如何在Oracle SQL中查询显示指定格式的表结构描述信息

你之前使用的ALL_TAB_COMMENTS视图仅存储表级注释信息，要获取包含字段名、类型、可空属性、默认值、字段序号、字段注释的完整列结构，需要关联列属性和列注释对应的系统视图查询。

用到的系统视图 #

• ALL_TAB_COLUMNS：存储当前用户可访问的所有表的列基础属性，包含列名、数据类型、长度/精度、是否允许为空、默认值、列序号等信息
• ALL_COL_COMMENTS：存储当前用户可访问的所有表的列注释信息

可直接执行的查询SQL #

SELECT 
  t.COLUMN_NAME AS "Name",
  -- 拼接带长度/精度的字段类型
  CASE 
    WHEN t.DATA_TYPE IN ('VARCHAR2', 'CHAR', 'NCHAR', 'NVARCHAR2') THEN t.DATA_TYPE || '(' || t.CHAR_LENGTH || ')'
    WHEN t.DATA_TYPE = 'NUMBER' AND t.DATA_PRECISION IS NOT NULL THEN 
      t.DATA_TYPE || '(' || t.DATA_PRECISION || CASE WHEN t.DATA_SCALE > 0 THEN ',' || t.DATA_SCALE ELSE '' END || ')'
    ELSE t.DATA_TYPE
  END AS "Type",
  -- 转换可空属性显示值
  CASE WHEN t.NULLABLE = 'N' THEN 'NOT NULL' ELSE '' END AS "Nullable",
  t.DATA_DEFAULT AS "Default",
  t.COLUMN_ID AS "Id",
  c.COMMENTS AS "Comments"
FROM ALL_TAB_COLUMNS t
LEFT JOIN ALL_COL_COMMENTS c
  ON t.OWNER = c.OWNER 
  AND t.TABLE_NAME = c.TABLE_NAME 
  AND t.COLUMN_NAME = c.COLUMN_NAME
WHERE 
  -- 如果需要指定schema，取消下一行注释替换为实际schema名
  -- t.OWNER = 'YOUR_SCHEMA_NAME'
  t.TABLE_NAME = 'ABC' -- Oracle数据字典默认表名存储为大写，建表时用引号指定小写的话此处需对应修改
ORDER BY t.COLUMN_ID;

补充说明 #

• 如果查询的是当前登录用户自身schema下的表，可以把视图替换为USER_TAB_COLUMNS、USER_COL_COMMENTS，不需要指定OWNER条件，查询效率更高。
• 如果需要同时展示表级注释，可以额外关联ALL_TAB_COMMENTS视图，新增字段取表注释值即可。
• 查询输出的字段顺序、字段名和你给出的期望格式完全一致。

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