oracle字典表导出,Oracle导出数据字典SQL语句

1、标注外键

SELECT S.*,

PK.*,

CASE

WHEN PK.COLUMN_POSITION > 0 THEN

‘√‘

ELSE

‘‘

END AS PKYN

FROM (SELECT A.OWNER,

A.TABLE_NAME,

A.COLUMN_NAME,

A.COLUMN_ID,

DECODE(A.CHAR_LENGTH,

0,

DECODE(A.DATA_SCALE,

NULL,

A.DATA_TYPE,

A.DATA_TYPE || ‘(‘ || A.DATA_PRECISION || ‘,‘ ||

A.DATA_SCALE || ‘)‘),

A.DATA_TYPE || ‘(‘ || A.CHAR_LENGTH || ‘)‘) AS COLTYPE,

A.DATA_DEFAULT AS DEFAULTVAL,

CASE

WHEN A.NULLABLE = ‘Y‘ THEN

‘√‘

ELSE

‘‘

END AS NULLYN,

B.COMMENTS AS COMMENTSS

FROM SYS.ALL_TAB_COLUMNS A, SYS.DBA_COL_COMMENTS B

WHERE A.OWNER = B.OWNER

AND A.TABLE_NAME = B.TABLE_NAME

AND A.COLUMN_NAME = B.COLUMN_NAME

AND A.OWNER = ‘ORDERS‘

AND A.TABLE_NAME = ‘T_ORDER‘) S,

(SELECT AA.INDEX_OWNER,

AA.TABLE_NAME,

AA.COLUMN_POSITION,

AA.COLUMN_NAME

FROM ALL_IND_COLUMNS AA, ALL_CONSTRAINTS BB

WHERE BB.CONSTRAINT_TYPE = ‘P‘

AND AA.TABLE_NAME = BB.TABLE_NAME

AND AA.INDEX_NAME = BB.CONSTRAINT_NAME

AND AA.INDEX_OWNER=BB.OWNER) PK

WHERE S.OWNER = PK.INDEX_OWNER(+)

AND S.TABLE_NAME = PK.TABLE_NAME(+)

AND S.COLUMN_NAME = PK.COLUMN_NAME(+)

ORDER BY S.TABLE_NAME, S.COLUMN_ID;

2、简单

SELECT COL.TABLE_NAME,

TT.COMMENTS,

COL.COLUMN_NAME AS COLUMNNAME,

CASE

WHEN PKCOL.COLUMN_POSITION > 0 THEN

‘√‘

ELSE

‘‘

END AS ISPK,

COL.DATA_TYPE AS TYPENAME,

COL.DATA_LENGTH 占用字节数,

COL.DATA_PRECISION AS PRECI,

COL.DATA_SCALE AS SCALE,

CASE

WHEN COL.NULLABLE = ‘Y‘ THEN

‘√‘

ELSE

‘‘

END AS CISNULL,

COL.DATA_DEFAULT AS DEFAULTVAL,

CCOM.COMMENTS AS DETEXT

FROM USER_TAB_COLUMNS COL,

USER_COL_COMMENTS CCOM,

(SELECT AA.TABLE_NAME,

AA.INDEX_NAME,

AA.COLUMN_NAME,

AA.COLUMN_POSITION

FROM USER_IND_COLUMNS AA, USER_CONSTRAINTS BB

WHERE BB.CONSTRAINT_TYPE = ‘P‘

AND AA.TABLE_NAME = BB.TABLE_NAME

AND AA.INDEX_NAME = BB.CONSTRAINT_NAME) PKCOL,

USER_TAB_COMMENTS TT

WHERE COL.TABLE_NAME = CCOM.TABLE_NAME

AND COL.COLUMN_NAME = CCOM.COLUMN_NAME

AND COL.TABLE_NAME = TT.TABLE_NAME(+)

AND COL.COLUMN_NAME = PKCOL.COLUMN_NAME(+)

AND COL.TABLE_NAME = PKCOL.TABLE_NAME(+)

ORDER BY COL.TABLE_NAME, COL.COLUMN_ID;

原文：https://www.cnblogs.com/ritchy/p/12219046.html