oracle数据库查表_Oracle查询数据库中的所有表

SELECT A.TABLE_NAME 表英文名,

A.TAB_COMMENTS 表中文名,

A.COLUMN_ID 序号,

A.COLUMN_NAME 英文名,

A.COMMENTS 中文名,

B.PRIMARY_KEY 主键,

substr(A.DATA_TYPE,1,instr(A.DATA_TYPE,'(')-1) 数据类型,

case when substr(A.DATA_TYPE,1,instr(A.DATA_TYPE,'(')-1) = 'TIMESTAMP' then '' when substr(A.DATA_TYPE,1,instr(A.DATA_TYPE,'(')-1) = 'DATATIME' then '' else  substr(A.DATA_TYPE,instr(A.DATA_TYPE,'(')+1,length(A.DATA_TYPE)-instr(A.DATA_TYPE,'(')-1) end 长度,

'' 单位,

A.NULLABLE 是否可空,

'' 缺省,

'' 上限,

'' 下限

FROM (SELECT X.TABLE_NAME,

Z.COMMENTS TAB_COMMENTS,

X.COLUMN_ID,

X.COLUMN_NAME,

X.DATA_TYPE ||

DECODE(X.DATA_TYPE,

'NUMBER',

DECODE(SIGN(X.DATA_SCALE),

0,

'(' ||

DECODE(LENGTH(TO_CHAR(X.DATA_PRECISION)),

1,

TO_CHAR(X.DATA_PRECISION),

TO_CHAR(X.DATA_PRECISION, 'FM00')) || ')',

1,

'(' ||

DECODE(LENGTH(TO_CHAR(X.DATA_PRECISION)),

1,

TO_CHAR(X.DATA_PRECISION),

TO_CHAR(X.DATA_PRECISION, 'FM00')) || ',' ||

TO_CHAR(X.DATA_SCALE, 'FM0') || ')',

''),

'(' || DECODE(LENGTH(TO_CHAR(X.DATA_LENGTH)),

1,

TO_CHAR(X.DATA_LENGTH),

TO_CHAR(X.DATA_LENGTH, 'FM9900')) || ')') AS DATA_TYPE,

X.NULLABLE,

Y.COMMENTS

FROM USER_TAB_COLUMNS X, USER_COL_COMMENTS Y, USER_TAB_COMMENTS Z

WHERE X.TABLE_NAME = Y.TABLE_NAME

AND X.COLUMN_NAME = Y.COLUMN_NAME

AND X.TABLE_NAME = Z.TABLE_NAME) A,

(SELECT X.TABLE_NAME TABLE_NAME,

X.COLUMN_NAME COLUMN_NAME,

MAX(DECODE(Y.CONSTRAINT_TYPE,

'P',

'PK' || TO_CHAR(X.POSITION, 'FM9'),

'')) PRIMARY_KEY,

MAX(DECODE(Y.CONSTRAINT_TYPE,

'R',

'FK' || TO_CHAR(X.POSITION, 'FM9'),

'')) FOREIGN_KEY

FROM USER_CONS_COLUMNS X, USER_CONSTRAINTS Y

WHERE X.TABLE_NAME = Y.TABLE_NAME

AND X.CONSTRAINT_NAME = Y.CONSTRAINT_NAME

AND Y.CONSTRAINT_TYPE <> 'C'

GROUP BY X.TABLE_NAME, X.COLUMN_NAME) B

WHERE A.TABLE_NAME = B.TABLE_NAME(+)

AND A.COLUMN_NAME = B.COLUMN_NAME(+)

/*AND A.table_name = upper('tp_planmonth')*/

ORDER BY A.TABLE_NAME, A.COLUMN_ID