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'