oracle中查询表信息整理

由于工作中涉及到查询其他用户下某个表的表结构，特此整理几种方法，以做记录。

1.通过DBA_TAB_COLUMNS、DBA_COL_COMMENTS、DBA_TAB_COMMENTS关联查询

SELECT C.TABLE_NAME 表名, T.COMMENTS 表注释, CC.COLUMN_NAME 字段名,
       CC.COMMENTS 字段注释, C.DATA_TYPE 字段类型, C.DATA_LENGTH 字段长度, C.DATA_SCALE,
       C.NULLABLE 是否为空,C.OWNER 用户
FROM DBA_TAB_COLUMNS C
LEFT JOIN DBA_COL_COMMENTS CC
ON C.TABLE_NAME = CC.TABLE_NAME
AND C.COLUMN_NAME = CC.COLUMN_NAME
LEFT JOIN DBA_TAB_COMMENTS T
ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.OWNER = '用户'
AND C.TABLE_NAME NOT LIKE 'BIN$%==$0'
AND C.TABLE_NAME = '表名'
AND CC.column_name = '字段名（大写）'
GROUP BY C.TABLE_NAME, T.COMMENTS, CC.COLUMN_NAME, CC.COMMENTS, C.DATA_TYPE,
         C.DATA_LENGTH, C.DATA_SCALE, C.NULLABLE, C.OWNER
ORDER BY C.TABLE_NAME;

2.通过ALL_TAB_COLUMNS直接查询

SELECT OWNER, COLUMN_ID, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH,
       NULLABLE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = '表名'
AND COLUMN_NAME = '字段名'
AND OWNER IN (用户)
ORDER BY TABLE_NAME DESC;

用下来，比较推荐第一种，虽然涉及表较多，但数据比较全面，可能比较直观的看到相关表结构数据。