기억력이 나쁜 나에게 주는 페이지

SELECT * FROM (
SELECT (CASE WHEN MAIN.COLUMN_ID = 1 THEN MAIN.TABLE_COMMENTS ELSE '' END)  table_kor_name
     , (CASE WHEN MAIN.COLUMN_ID = 1 THEN MAIN.TABLE_NAME ELSE '' END ) table_eng_name
     , MAIN.COLUMN_ID column_id
     , MAIN.COLUMN_NAME column_name
     , MAIN.COLUMN_COMMENTS column_comments
     , (CASE WHEN MAIN.DATA_TYPE = 'VARCHAR2' THEN 'VARCHAR' ELSE MAIN.DATA_TYPE END ) AS data_type
     , (CASE MAIN.DATA_TYPE WHEN 'NUMBER' THEN ''
                            WHEN 'DATE'   THEN '' ELSE TO_CHAR(MAIN.DATA_LENGTH) END) data_type_length
     , (CASE NVL(MAIN.NULL_FLAG, 'N') WHEN 'N' THEN 'N' ELSE '' END) null_flag
     , (SELECT CONST.CONSTRAINT_NAME FROM USER_CONSTRAINTS CONST, USER_CONS_COLUMNS COLS, USER_CONS_COLUMNS R_COLS
         WHERE CONST.CONSTRAINT_NAME   = COLS.CONSTRAINT_NAME
           AND CONST.CONSTRAINT_TYPE = 'P'
           AND CONST.R_CONSTRAINT_NAME = R_COLS.CONSTRAINT_NAME(+)
           AND CONST.TABLE_NAME  = MAIN.TABLE_NAME
           AND COLS.COLUMN_NAME = MAIN.COLUMN_NAME) AS PK
      , ( SELECT DISTINCT CONST.CONSTRAINT_NAME||' ['||COLS.COLUMN_NAME||'('||CONST.R_CONSTRAINT_NAME||')]'
              FROM USER_CONSTRAINTS CONST
                 , USER_CONS_COLUMNS COLS
                 , USER_CONS_COLUMNS R_COLS
             WHERE CONST.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
               AND CONST.CONSTRAINT_TYPE = 'R'
               AND CONST.R_CONSTRAINT_NAME = R_COLS.CONSTRAINT_NAME(+)
               AND CONST.TABLE_NAME  = MAIN.TABLE_NAME
               AND COLS.COLUMN_NAME = MAIN.COLUMN_NAME
              -- AND rownum < 2
          ) AS FK
--     , (CASE SUB.CONSTRAINT_TYPE WHEN 'P' THEN 'PK' WHEN 'R' THEN 'FK ('||SUB.R_CONST_NAME||')' END) key_flag
  FROM (SELECT T_COMT.COMMENTS AS table_comments
             , T_COLS.TABLE_NAME AS table_name
             , C_COMT.COMMENTS AS column_comments
             , T_COLS.COLUMN_NAME AS column_name
             , (CASE T_COLS.NULLABLE WHEN 'Y' THEN 'Y' END) AS null_flag
             , T_COLS.DATA_TYPE AS data_type
             , T_COLS.DATA_LENGTH AS data_length
             , T_COLS.COLUMN_ID AS column_id
             , T_COLS.DATA_PRECISION AS data_precision
             , T_COLS.DATA_SCALE AS data_scale
          FROM USER_TAB_COLUMNS T_COLS
             , USER_TAB_COMMENTS T_COMT
             , USER_COL_COMMENTS C_COMT
         WHERE T_COLS.TABLE_NAME  = T_COMT.TABLE_NAME
           AND T_COLS.TABLE_NAME  = C_COMT.TABLE_NAME
           AND T_COLS.COLUMN_NAME = C_COMT.COLUMN_NAME
           AND T_COMT.TABLE_TYPE  = 'TABLE' ) MAIN
 WHERE MAIN.TABLE_NAME  = UPPER('테이블명')  /*특정 테이블 조회일 경우 사용.*/
)
 ORDER BY table_eng_name, PK, FK, COLUMN_ID 
;


-- 인덱스 조회
SELECT c.index_name
     , c.column_name
     , c.column_position
     , i.uniqueness 
  FROM user_indexes i
     , user_ind_columns c 
 WHERE c.index_name = i.index_name 
   AND c.table_name = '테이블명'; 


-- Foreing Key가 여러개 일 경우 조회
select distinct *
  from (
 SELECT CONST.CONSTRAINT_NAME, COLS.COLUMN_NAME, cols.position
  FROM USER_CONSTRAINTS CONST
     , USER_CONS_COLUMNS COLS
     , USER_CONS_COLUMNS R_COLS
 WHERE CONST.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
   AND CONST.CONSTRAINT_TYPE = 'R'
   AND CONST.R_CONSTRAINT_NAME = R_COLS.CONSTRAINT_NAME(+)
   AND CONST.TABLE_NAME  = '테이블명'
               --AND COLS.COLUMN_NAME = MAIN.COLUMN_NAME
 order by const.constraint_name, cols.position
 )
  order by constraint_name, position