oracle 通过 sql 获取表结构信息

通过 SQL 获取 Oracle 表 字段信息

`

SELECT A.OWNER 用户，
A.COLUMN_ID 字段序号，
A.TABLE_NAME 表名，
A.COLUMN_NAME 字段名，
NVL(B.COMMENTS, '') 字段备注，
A.DATA_TYPE 数据类型，
A.NULLABLE 是否允许为空，
C.COMMENTS 表备注，
CASE
WHEN A2.COLUMN_NAME IS NOT NULL THEN
'Y'
ELSE
'N'
END 是否为主键，
CASE
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_PRECISION IS NOT NULL THEN
'NUMBER' || '(' || TO_CHAR(A.DATA_PRECISION) || ',' ||
TO_CHAR(A.DATA_SCALE) || ')'
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_PRECISION IS NULL THEN
'NUMBER'
WHEN A.DATA_TYPE = 'DATE' THEN
'DATE'
WHEN A.DATA_TYPE = 'VARCHAR' THEN
'VARCHAR(' || TO_CHAR(A.DATA_LENGTH) || ')'
WHEN A.DATA_TYPE = 'VARCHAR2' THEN
'VARCHAR2(' || TO_CHAR(A.DATA_LENGTH) || ')'
WHEN A.DATA_TYPE = 'CHAR' THEN
'CHAR(' || TO_CHAR(A.DATA_LENGTH) || ')'
ELSE
A.DATA_TYPE
END AS COLUMN_LENGTH,
CASE
WHEN S.COLUMN_NAME IS NULL THEN
'N'
WHEN S.COLUMN_NAME IS NOT NULL THEN
'Y'
END AS 是否为分区字段

FROM ALL_TAB_COLS A
INNER JOIN ALL_COL_COMMENTS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.OWNER = B.OWNER
INNER JOIN ALL_TAB_COMMENTS C
ON A.OWNER = C.OWNER
AND A.TABLE_NAME = C.TABLE_NAME

LEFT JOIN ALL_PART_KEY_COLUMNS S
ON A.OWNER = S.OWNER
AND A.TABLE_NAME = S.NAME
AND A.COLUMN_NAME = S.COLUMN_NAME
AND S.OBJECT_TYPE = 'TABLE'

LEFT JOIN (SELECT C.TABLE_NAME, C.COLUMN_NAME, C.POSITION, C.OWNER
FROM ALL_CONS_COLUMNS C, ALL_CONSTRAINTS D
WHERE C.CONSTRAINT_NAME = D.CONSTRAINT_NAME
AND D.CONSTRAINT_TYPE = 'P') A2
ON A.OWNER = A2.OWNER
AND A.TABLE_NAME = A2.TABLE_NAME
AND A.COLUMN_NAME = A2.COLUMN_NAME

WHERE A.OWNER = '***'

AND A.TABLE_NAME ='****'

ORDER BY A.TABLE_NAME, A.COLUMN_ID;

`
以上 sql 也可以批量导出 oracle 表结构信息