Oracle查询表字段信息及注释

查询字段信息：

SELECT * FROM all_tab_columns WHERE OWNER = OWNER_NAME AND TABLE_NAME=TABLE_NAME

-- 或者

SELECT * FROM user_tab_columns WHERE TABLE_NAME=TABLE_NAME

查询字段注释：

SELECT * FROM all_col_comments WHERE TABLE_NAME=TABLE_NAME

--或者

SELECT * FROM user_col_comments WHERE TABLE_NAME=TABLE_NAME

合起来查询：

注意：查询结果中，字段非空是 ‘N’，可为空是 ‘Y’

SELECT 
  a.OWNER 模式,
  a.TABLE_NAME 表名, 
  a.COLUMN_NAME 列名, 
  a.DATA_TYPE 数据类型, 
  a.DATA_LENGTH 长度, 
  a.NULLABLE 非空, 
  --(CASE WHEN a.NULLABLE = N THEN Y ELSE N END) 非空,
  b.COMMENTS 注释
FROM all_tab_columns a
LEFT JOIN all_col_comments b ON a.OWNER = b.OWNER AND a.TABLE_NAME = b.TABLE_NAME AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.OWNER = OWNER_NAME AND a.TABLE_NAME = TABLE_NAME 
ORDER BY a.TABLE_NAME, a.COLUMN_ID

--或者

SELECT 
  a.TABLE_NAME 表名, 
  a.COLUMN_NAME 列名, 
  a.DATA_TYPE 数据类型, 
  a.DATA_LENGTH 长度, 
  a.NULLABLE 非空, 
  --(CASE WHEN a.NULLABLE = N THEN Y ELSE N END) 非空,
  b.COMMENTS 注释
FROM user_tab_columns a
LEFT JOIN user_col_comments b ON a.TABLE_NAME = b.TABLE_NAME AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.TABLE_NAME = TABLE_NAME 
ORDER BY a.COLUMN_ID

另，查询表注释：

SELECT * FROM user_tab_comments WHERE TABLE_NAME=CODE_GENERAL