mysql 读取oracle数据_Oracle/SQL Server/MySQL获取表及字段数据字典

Oracle数据库

测试平台 - Database 11gR2/12cR2

--数据字典表级信息

SELECT USER,T.TABLE_NAME,TC.COMMENTS,DECODE(C.CONSTRAINT_NAME,NULL,'N','Y') PK,

I.INDEX_COLS UNIQUE_INDEXES

FROM   USER_TABLES T,

USER_TAB_COMMENTS TC,

USER_CONSTRAINTS C,

(SELECT TABLE_NAME, UNIQUENESS, LISTAGG(INDEX_COLS, ';') WITHIN GROUP (ORDER BY INDEX_NAME                                                            ) INDEX_COLS

FROM

(SELECT I.TABLE_NAME, I.UNIQUENESS, I.INDEX_NAME, I.INDEX_NAME||'('||

(LISTAGG(IC.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY IC.COLUMN_POSITION))||')' I                                                            NDEX_COLS

FROM   USER_INDEXES I,

USER_IND_COLUMNS IC

WHERE  I.INDEX_NAME = IC.INDEX_NAME

AND    I.UNIQUENESS = 'UNIQUE'

GROUP BY I.TABLE_NAME, I.INDEX_NAME, I.UNIQUENESS

)

GROUP BY TABLE_NAME, UNIQUENESS

) I

WHERE  T.TABLE_NAME = TC.TABLE_NAME(+)

AND    T.TABLE_NAME = C.TABLE_NAME(+)

AND    C.CONSTRAINT_TYPE(+) = 'P'

AND    T.TABLE_NAME = I.TABLE_NAME(+)

ORDER BY TABLE_NAME

;

--字段级信息

SELECT USER SCHEMA_NAME, T.TABLE_NAME, C.COMMENTS, TC.COLUMN_ID, TC.COLUMN_NAME, CC.COMMENTS,

CASE

WHEN TC.DATA_TYPE IN ('CHAR','VARCHAR2','NVARCHAR2') THEN

TC.DATA_TYPE||'('||TC.CHAR_LENGTH||')'

WHEN TC.DATA_TYPE IN ('NUMBER') THEN

TC.DATA_TYPE||DECODE(DATA_PRECISION,NULL,NULL,'('||DATA_PRECISION||

DECODE(DATA_SCALE,NULL,NULL,0,NULL,','||DATA_SCALE)||')')

ELSE

TC.DATA_TYPE

END DATA_TYPE,

DECODE(PK_COL.COLUMN_NAME,NULL,'N','Y') PK_COLS, TC.NULLABLE

FROM