반응형
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)
반응형
'IT' 카테고리의 다른 글
인터넷 해지/가입하며 알아버린 불편한 사실들 (7) | 2010.08.30 |
---|---|
네이트온과 비교되는 Gmail 의 화상채팅 (0) | 2010.06.02 |
☆ 제목만 클릭해도 악성코드에 감염되는 해킹메일 주의 (0) | 2010.01.28 |
오라클 에러코드 (2) | 2009.12.04 |
오라클(ORACLE) HINT 모음, 튜닝, sql (6) | 2009.12.01 |