1. 테이블 및 컬럼 정보
- 현재 데이터베이스 조회 :
SELECT NAME FROM V$DATABASE;
- OBJECT 조회 :
SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = [오브젝트명];
- SYNONYM 조회 :
SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME = [시노님명];
- INDEX 조회 :
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = [인덱스명];
1-1. 테이블 정보 조회
SELECT * FROM ALL_TABLES;
SELECT * FROM DBA_TABLES;
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
SELECT * FROM TABS;
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';
SELECT * FROM USER_TABLES;
SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE '%%';
1-2. 컬럼 정보 조회
SELECT * FROM COLS WHERE TABLE_NAME LIKE '%%';
SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE '%%';
1-3. 테이블 및 컬럼 코멘트
COMMENT ON TABLE [테이블명] IS '코멘트';
COMMENT ON COLUMN [테이블명].[컬럼명] IS '코멘트';
SELECT * FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME LIKE '%USER_INFO%';
SELECT * FROM ALL_COL_COMMENTS
WHERE TABLE_NAME LIKE '%USER_INFO%';
1-3-1. 테이블, 컬럼 정보 조회 쿼리
SELECT
TC.TABLE_NAME
, TC.COMMENTS
, CC.COLUMN_NAME
, CC.COMMENTS
, C.DATA_TYPE
, C.DATA_LENGTH
, C.NULLABLE
, C.DATA_DEFAULT
FROM ALL_TAB_COMMENTS TC
LEFT JOIN ALL_TAB_COLUMNS C
ON TC.OWNER = C.OWNER
AND TC.TABLE_NAME = C.TABLE_NAME
LEFT JOIN ALL_COL_COMMENTS CC
ON TC.OWNER = CC.OWNER
AND TC.TABLE_NAME = CC.TABLE_NAME
AND C.TABLE_NAME = CC.TABLE_NAME
AND C.COLUMN_NAME = CC.COLUMN_NAME
WHERE TC.TABLE_NAME LIKE '[테이블명]';