2013. 12. 13. 16:38 DB/ORACLE
접속한 사용자의 테이블 컬럼 정보 보는 쿼리
SELECT UTC.TABLE_NAME,
UTC.COLUMN_NAME AS PHYSICAL_NAME,
(SELECT CASE WHEN UCC.COLUMN_NAME IS NOT NULL THEN 'PK'
ELSE ''
END
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS CC
WHERE UC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
AND UTC.TABLE_NAME = UC.TABLE_NAME
AND UTC.COLUMN_NAME = CC.COLUMN_NAME) AS PK,
UTC.DATA_TYPE ||
CASE
WHEN UTC.DATA_TYPE = 'NUMBER' THEN '(' || UTC.DATA_PRECISION || ', ' || UTC.DATA_SCALE || ')'
ELSE CASE WHEN UTC.CHAR_LENGTH != 0 THEN '(' || UTC.CHAR_LENGTH || ')' ELSE NULL END
END AS DATA_TYPE,
UCC.COMMENTS AS LOGICAL_NAME,
--T.DATA_SCALE,
UTC.NULLABLE,
UTC.DATA_DEFAULT
FROM USER_TABLES UT,
USER_TAB_COLUMNS UTC,
USER_COL_COMMENTS UCC
WHERE UT.TABLE_NAME = UTC.TABLE_NAME
AND UTC.TABLE_NAME = UCC.TABLE_NAME
AND UTC.COLUMN_NAME = UCC.COLUMN_NAME
ORDER BY UTC.TABLE_NAME, UTC.COLUMN_NAME ASC;
'DB > ORACLE' 카테고리의 다른 글
UBUNTU 12.04에서 ORACLE 11gR2 설치하기 (0) | 2013.11.15 |
---|---|
Oracle lock 확인 및 kill 방법 (0) | 2012.12.27 |
windows 7 에서 Oracle 11gR2 Client 삭제하기 (0) | 2012.01.09 |
오라클 CHARSET 확인, 변경 (0) | 2010.04.01 |