반응형
SELECT C.TABLE_NAME AS TABLE_NAME
, B.COMMENTS AS TABLE_DESC
, C.COLUMN_ID AS COLUMN_ID
, C.COLUMN_NAME AS COLUMN_NAME
, SUBSTR(D.COMMENTS,1,120) AS COLUMN_DESC
, E.PRIMARY_KEY AS PRIMARY_KEY
, C.DATA_TYPE AS DATA_TYPE
, CASE
WHEN DATA_TYPE IN ('VARCHAR2','CHAR') THEN TO_CHAR(DATA_LENGTH)
WHEN DATA_SCALE IS NULL OR DATA_SCALE = 0 THEN TO_CHAR(DATA_PRECISION)
ELSE TO_CHAR(DATA_PRECISION) || ',' || TO_CHAR(DATA_SCALE)
END AS DATA_LENGTH
, C.NULLABLE AS NULLABLE
, C.DATA_DEFAULT
FROM USER_TABLES A,
USER_TAB_COMMENTS B,
USER_TAB_COLUMNS C,
USER_COL_COMMENTS D,
(SELECT TABLE_NAME AS TABLE_NAME ,
COLUMN_NAME AS COLUMN_NAME ,
POSITION AS PRIMARY_KEY
FROM USER_CONS_COLUMNS
WHERE (TABLE_NAME,
CONSTRAINT_NAME) IN (SELECT TABLE_NAME,
CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = DECODE(:TABLE_NAME ,NULL, TABLE_NAME, :TABLE_NAME)
AND CONSTRAINT_TYPE = 'P')
) E
WHERE A.TABLE_NAME = B.TABLE_NAME
AND B.TABLE_TYPE = 'TABLE'
AND B.TABLE_NAME = C.TABLE_NAME
AND C.TABLE_NAME = D.TABLE_NAME
AND C.COLUMN_NAME = D.COLUMN_NAME
AND C.TABLE_NAME = E.TABLE_NAME (+)
AND C.COLUMN_NAME = E.COLUMN_NAME(+)
AND C.TABLE_NAME = DECODE(:TABLE_NAME ,NULL, C.TABLE_NAME, :TABLE_NAME)
ORDER BY C.TABLE_NAME
, B.COMMENTS
, C.COLUMN_ID
, (CASE
WHEN (CASE
WHEN DATA_TYPE IN ('VARCHAR2','CHAR') THEN TO_CHAR(DATA_LENGTH)
WHEN DATA_SCALE IS NULL OR DATA_SCALE = 0 THEN TO_CHAR(DATA_PRECISION)
ELSE TO_CHAR(DATA_PRECISION) || ',' || TO_CHAR(DATA_SCALE)
END) IS NULL THEN C.DATA_TYPE
ELSE C.DATA_TYPE || '(' ||
(CASE
WHEN DATA_TYPE IN ('VARCHAR2','CHAR') THEN TO_CHAR(DATA_LENGTH)
WHEN DATA_SCALE IS NULL OR DATA_SCALE = 0 THEN TO_CHAR(DATA_PRECISION)
ELSE TO_CHAR(DATA_PRECISION) || ',' || TO_CHAR(DATA_SCALE)
END) || ')'
END)
반응형
