[Oracle] 테이블 정보 조회하기

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