oracle tmop

인덱스 정보 얻기

SELECT indexes.TABLE_NAME, indexes.INDEX_NAME, indexes.INDEX_TYPE,
             columns.COLUMN_POSITION, columns.COLUMN_NAME, columns.DESCEND
FROM USER_INDEXES indexes, USER_IND_COLUMNS columns
WHERE indexes.INDEX_NAME = columns.INDEX_NAME
    AND indexes.TABLE_NAME like '테이블명'
ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION

테이블 정보 얻기

SELECT tbl.TABLE_NAME, comments.COMMENTS, tbl.TABLESPACE_NAME
FROM USER_TABLES tbl, USER_TAB_COMMENTS comments
WHERE tbl.TABLE_NAME = comments.TABLE_NAME
ORDER BY tbl.TABLE_NAME

컬럼정보 얻기

SELECT tab_columns.TABLE_NAME,
    tab_columns.COLUMN_ID,
    tab_columns.COLUMN_NAME,
    (case
      when DATA_TYPE like '%CHAR%' then DATA_TYPE || '(' || DATA_LENGTH || ')'
      when DATA_TYPE = 'NUMBER' and DATA_PRECISION > 0 and DATA_SCALE > 0 then DATA_TYPE || '(' || DATA_PRECISION || ',' || DATA_SCALE || ')'
      when DATA_TYPE = 'NUMBER' and DATA_PRECISION > 0 then DATA_TYPE || '(' || DATA_PRECISION || ')'
      when DATA_TYPE = 'NUMBER' then DATA_TYPE
      else DATA_TYPE
    end) DATA_TYPE,
    decode(NULLABLE, 'N', 'Not Null', 'Null') NULLABLE,
    DATA_DEFAULT,
    (
      SELECT decode(
        sum((
          SELECT decode(CONSTRAINT_TYPE, 'P', 1, 'R', 2, 0)
          FROM USER_CONSTRAINTS
          WHERE CONSTRAINT_NAME = cons_columns.CONSTRAINT_NAME
        ))
        , 1, 'PK', 2, 'FK', 3, 'PK, FK', '')
      FROM USER_CONS_COLUMNS cons_columns
      WHERE TABLE_NAME = tab_columns.TABLE_NAME AND COLUMN_NAME = tab_columns.COLUMN_NAME
    ) CONSTRAINTS,
    comments.COMMENTS
FROM USER_TAB_COLUMNS tab_columns, USER_COL_COMMENTS comments
WHERE tab_columns.TABLE_NAME = comments.TABLE_NAME(+) AND tab_columns.COLUMN_NAME = comments.COLUMN_NAME(+)
  AND tab_columns.TABLE_NAME = '테이블명'
ORDER BY COLUMN_ID