오라클 스키마 조회 쿼리

오라클 데이터베이스 리뷰에 유용한 스키마 조회 쿼리입니다.

 

오라클.스키마조회.쿼리.txt

-----------------------------------------------------------
-- TABLE 조회
-----------------------------------------------------------
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
  FROM ALL_TABLES
 WHERE OWNER = 'MYNAME';

SELECT *
  FROM ALL_OBJECTS
 WHERE OWNER = 'MYNAME';

SELECT SUBSTR(TABLE_NAME, 1, 2) AS AREA1
     , SUBSTR(TABLE_NAME, 4, 1) AS AREA2
     , SUBSTR(TABLE_NAME, 4, 3) AS AREA3
     , TABLE_NAME
     , COMMENTS
  FROM ALL_TAB_COMMENTS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%';

-----------------------------------------------------------
-- COLUMN 조회
-----------------------------------------------------------
SELECT OWNER, TABLE_NAME, COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE
  FROM ALL_TAB_COLUMNS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%';

SELECT *
  FROM ALL_COL_COMMENTS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%';

SELECT A.OWNER, A.TABLE_NAME, A.COLUMN_NAME, B.COMMENTS, A.DATA_TYPE, A.DATA_LENGTH
  FROM ALL_TAB_COLUMNS A,
       ALL_COL_COMMENTS B
 WHERE A.OWNER = 'MYNAME'
   AND A.TABLE_NAME LIKE 'D%'
   AND A.TABLE_NAME = B.TABLE_NAME
   AND A.COLUMN_NAME = B.COLUMN_NAME;

-----------------------------------------------------------
-- COLUMN NAME 기준 COMMENTS 중복 조회
-----------------------------------------------------------
SELECT *
  FROM ALL_COL_COMMENTS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%'
   AND COLUMN_NAME IN (SELECT COLUMN_NAME
                         FROM (SELECT COLUMN_NAME, SUM(CNT) AS CNT
                                 FROM (SELECT DISTINCT A.COLUMN_NAME, B.COMMENTS, 1 AS CNT
                                         FROM ALL_TAB_COLUMNS A,
                                              ALL_COL_COMMENTS B
                                        WHERE A.OWNER = 'MYNAME'
                                          AND A.TABLE_NAME LIKE 'D%'
                                          AND A.TABLE_NAME = B.TABLE_NAME
                                          AND A.COLUMN_NAME = B.COLUMN_NAME
                                          AND B.COMMENTS IS NOT NULL
                                      )
                                GROUP BY COLUMN_NAME
                              ) WHERE CNT > 1
                      );

-----------------------------------------------------------
-- COLUMN COMMENTS 기준 NAME 중복 조회
-----------------------------------------------------------
SELECT DISTINCT COLUMN_NAME, COMMENTS
  FROM ALL_COL_COMMENTS
 WHERE OWNER = 'MYNAME'
   AND TABLE_NAME LIKE 'D%'
   AND COMMENTS IN (SELECT COMMENTS
                      FROM (SELECT COMMENTS, SUM(CNT) AS CNT
                              FROM (SELECT DISTINCT A.COLUMN_NAME, B.COMMENTS, 1 AS CNT
                                      FROM ALL_TAB_COLUMNS A,
                                           ALL_COL_COMMENTS B
                                     WHERE A.OWNER = 'BIS_MGR'
                                       AND A.TABLE_NAME LIKE 'D%'
                                       AND A.TABLE_NAME = B.TABLE_NAME
                                       AND A.COLUMN_NAME = B.COLUMN_NAME
                                       AND B.COMMENTS IS NOT NULL
                                   )
                             GROUP BY COMMENTS
                           ) WHERE CNT > 1
                      );

-----------------------------------------------------------
-- COLUMN 계획 데이터 필드 조회
-----------------------------------------------------------
SELECT A.OWNER, A.TABLE_NAME, B.COMMENTS, A.COMMENTS
  FROM ALL_COL_COMMENTS A, ALL_TAB_COMMENTS B
 WHERE A.OWNER = 'MYNAME'
   AND A.TABLE_NAME LIKE 'D%'
   AND A.TABLE_NAME = B.TABLE_NAME
   AND (A.COLUMN_NAME LIKE '%PLAN%' OR A.COLUMN_NAME LIKE '%PLN%');