IT/데이터베이스
[Oracle] 테이블 정보 조회하기
forever story
2020. 10. 15. 14:07
반응형
Oracle 테이블 정보를 조회하는 쿼리 입니다.
OBJECT 조회
SELECT *
FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE 'OBJECT명'
TABLE 조회
SELECT *
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'TABLE명'
SYNONYM 조회
SELECT *
FROM ALL_SYNONYMS
WHERE SYNONYM_NAME = 'SYNONYM명'
Table Index 조회
SELECT *
FROM ALL_IND_COLUMNS
WHERE TABLE_NAME = 'TABLE명'
Table Column 조회
SELECT *
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'TABLE명'
Table Comment 조회
SELECT *
FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME = 'TABLE명'
Column Comment 조회
SELECT *
FROM ALL_COL_COMMENTS
WHERE TABLE_NAME = 'TABLE명'
Table 정의서 조회 응용 쿼리
SELECT A.COLUMN_ID AS NO
, B.COMMENTS AS "논리명"
, A.COLUMN_NAME AS "물리명"
, A.DATA_TYPE AS "자료 형태"
, A.DATA_LENGTH AS "길이"
, DECODE(A.NULLABLE, 'N', 'No', 'Y', 'Yes') AS "Null 허용"
, '' AS "식별자"
, A.DATA_DEFAULT AS "기본값"
, B.COMMENTS AS "코멘트"
FROM ALL_TAB_COLUMNS A
LEFT JOIN ALL_COL_COMMENTS B
ON A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
WHERE A.TABLE_NAME LIKE 'TABLE명'
ORDER BY A.COLUMN_ID
반응형