Oracle 테이블 명세서 쿼리

Oracle / Tibero 테이블 명세서 쿼리

-- 테이블 목록
SELECT TAB.TABLE_NAME, COM.COMMENTS, TO_CHAR(OBJ.CREATED,'YYYY-MM-DD') AS CREATE_DATE
  FROM USER_TABLES TAB, USER_TAB_COMMENTS COM, USER_OBJECTS OBJ
 WHERE TAB.TABLE_NAME = COM.TABLE_NAME
   AND TAB.TABLE_NAME = OBJ.OBJECT_NAME
   AND OBJ.OBJECT_TYPE = 'TABLE'
   -- AND TAB.TABLE_NAME IN (...)
 ORDER BY TAB.TABLE_NAME;

-- 컬럼 목록 (TABLE_NAME = '테이블명')
SELECT V.COLUMN_ID, V.COLUMN_NAME,
       CASE V.DATA_TYPE
            WHEN 'NUMBER' THEN CASE WHEN V.DATA_PRECISION IS NULL THEN 'NUMBER'
                                   ELSE 'NUMBER(' || V.DATA_PRECISION || ')' END
            WHEN 'CLOB' THEN V.DATA_TYPE
            WHEN 'DATE' THEN V.DATA_TYPE
            WHEN 'FLOAT' THEN 'FLOAT'
            WHEN 'TIMESTAMP(6)' THEN 'TIMESTAMP'
            ELSE V.DATA_TYPE || '(' || V.CHAR_LENGTH || ')' END AS DATA_TYPE,
       CASE WHEN V.CONSTRAINT_TYPE IS NOT NULL THEN 'Y' ELSE '' END AS PK,
       CASE WHEN V.RCOLUMN IS NOT NULL THEN 'Y' ELSE '' END AS FK,
       V.NULLABLE, V.DATA_DEFAULT, V.COMMENTS
  FROM (
        SELECT COL.COLUMN_ID, COL.COLUMN_NAME, COM.COMMENTS, TCOM.COMMENTS AS T_COMMENTS,
               COL.DATA_TYPE, COL.DATA_PRECISION, COL.CHAR_LENGTH, COL.NULLABLE, COL.DATA_DEFAULT,
               CON.CONSTRAINT_TYPE, FCON.RCOLUMN
          FROM USER_TAB_COLUMNS COL
               INNER JOIN USER_COL_COMMENTS COM
                 ON COL.TABLE_NAME = COM.TABLE_NAME AND COL.COLUMN_NAME = COM.COLUMN_NAME
               INNER JOIN USER_TAB_COMMENTS TCOM ON COL.TABLE_NAME = TCOM.TABLE_NAME
               LEFT JOIN (
                 SELECT C.TABLE_NAME, C.COLUMN_NAME, S.CONSTRAINT_TYPE
                   FROM USER_CONS_COLUMNS C
                   JOIN USER_CONSTRAINTS S ON C.CONSTRAINT_NAME = S.CONSTRAINT_NAME
                  WHERE S.CONSTRAINT_TYPE = 'P'
               ) CON ON CON.TABLE_NAME = COL.TABLE_NAME AND CON.COLUMN_NAME = COL.COLUMN_NAME
               LEFT JOIN (
                 SELECT C.TABLE_NAME, C.COLUMN_NAME, RC.TABLE_NAME||'.'||RC.COLUMN_NAME AS RCOLUMN
                   FROM USER_CONS_COLUMNS C
                   JOIN USER_CONSTRAINTS S
                     ON C.TABLE_NAME = S.TABLE_NAME AND C.CONSTRAINT_NAME = S.CONSTRAINT_NAME
                   JOIN USER_CONS_COLUMNS RC
                     ON S.R_CONSTRAINT_NAME = RC.CONSTRAINT_NAME AND C.POSITION = RC.POSITION
                  WHERE S.CONSTRAINT_TYPE = 'R'
               ) FCON ON FCON.TABLE_NAME = COL.TABLE_NAME AND FCON.COLUMN_NAME = COL.COLUMN_NAME
         WHERE COL.TABLE_NAME = '테이블명'
) V
 ORDER BY V.COLUMN_ID;

-- 제약조건
SELECT U.CONSTRAINT_NAME,
       CASE WHEN C.CONSTRAINT_TYPE = 'R' THEN 'Foreign Key'
            WHEN C.CONSTRAINT_TYPE = 'P' THEN 'Primary Key'
            ELSE 'Check' END AS TYPE,
       C.STATUS, U.COLUMN_NAME, C.SEARCH_CONDITION
  FROM USER_CONS_COLUMNS U
       JOIN USER_TAB_COLUMNS T ON U.TABLE_NAME = T.TABLE_NAME AND U.COLUMN_NAME = T.COLUMN_NAME
       JOIN USER_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
 WHERE C.GENERATED = 'USER NAME'
   AND C.CONSTRAINT_TYPE IN ('R','C','P')
   AND U.TABLE_NAME = '테이블명'
 ORDER BY C.CONSTRAINT_TYPE DESC, T.COLUMN_ID;

-- 인덱스
SELECT INDEX_NAME, UNIQUENESS, STATUS, INDEX_TYPE,
       LTRIM(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','), ',') AS COLUMNS
  FROM (
        SELECT INDEX_NAME, UNIQUENESS, STATUS, INDEX_TYPE, COLUMN_NAME,
               ROW_NUMBER() OVER(PARTITION BY INDEX_NAME ORDER BY COLUMN_POSITION) AS RN,
               COUNT(*) OVER(PARTITION BY INDEX_NAME) AS CNT
          FROM (
                SELECT I.INDEX_NAME, I.UNIQUENESS, I.STATUS, I.INDEX_TYPE,
                       IC.COLUMN_NAME, IC.COLUMN_POSITION
                  FROM USER_INDEXES I
                  JOIN USER_IND_COLUMNS IC ON I.INDEX_NAME = IC.INDEX_NAME
                 WHERE I.TABLE_NAME = '테이블명'
               )
       )
 WHERE RN = CNT
 START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1 AND PRIOR INDEX_NAME = INDEX_NAME
 ORDER BY UNIQUENESS DESC, INDEX_NAME;

-- 시퀀스
SELECT SEQUENCE_NAME, INCREMENT_BY, CYCLE_FLAG, CACHE_SIZE, LAST_NUMBER
  FROM USER_SEQUENCES
 WHERE SEQUENCE_NAME = 'PG_BUDGET_EVALUATION';