테이블 정보 query

SELECT A.TABLE_NAME

     , A.TABLE_COMMENTS

     , A.COLUMN_ID

     , A.COLUMN_NAME

     , D.COMMENTS      AS COLUMN_COMMENTS

     , A.DATA_TYPE

     , A.DATA_LENGTH

     , A.NULLABLE

  FROM (

        SELECT A.OWNER

             , B.TABLE_NAME

             , C.COMMENTS     AS TABLE_COMMENTS

             , B.COLUMN_NAME

             , B.DATA_TYPE

             , DECODE(B.DATA_TYPE,'NUMBER',DECODE(NVL(B.DATA_PRECISION,0),0,'',TO_CHAR(B.data_precision)||','||TO_CHAR(B.DATA_SCALE))

                                 ,'VARCHAR', B.CHAR_LENGTH

                                 ,'VARCHAR2', B.CHAR_LENGTH

                                 ,'CHAR', B.CHAR_LENGTH

                                 ,TO_CHAR(B.DATA_LENGTH) )    DATA_LENGTH

             , B.NULLABLE

             , B.COLUMN_ID

          FROM ALL_TABLES        A

             , ALL_TAB_COLUMNS   B

             , ALL_TAB_COMMENTS  C

         WHERE A.OWNER       = '소유자넣기'

           AND A.OWNER       = B.OWNER

           AND A.TABLE_NAME  = B.TABLE_NAME

           AND A.OWNER       = C.OWNER(+) 

           AND A.TABLE_NAME  = C.TABLE_NAME(+) 

      ) A

     , ALL_COL_COMMENTS  D

 WHERE A.OWNER       = D.OWNER(+)

   AND A.TABLE_NAME  = D.TABLE_NAME(+)

   AND A.COLUMN_NAME = D.COLUMN_NAME(+)

ORDER BY A.TABLE_NAME, A.COLUMN_ID

;

SELECT A.TABLE_NAME,
       A.COLUMN_ID,
       A.COLUMN_NAME,
       A.DATA_TYPE,
       A.NULLABLE,
       DECODE(A.DATA_TYPE,'NUMBER',DECODE(NVL(A.DATA_PRECISION,0),0,'',TO_CHAR(A.data_precision)||','||TO_CHAR(DATA_SCALE)),TO_CHAR(DATA_LENGTH)) DATA_LENGTH,
       B.COMMENTS,
       A.DATA_DEFAULT,
       A.OWNER
  FROM ALL_TAB_COLUMNS  A,
       ALL_COL_COMMENTS B
 WHERE B.OWNER       = A.OWNER
   AND B.TABLE_NAME  = A.TABLE_NAME
   AND B.COLUMN_NAME = A.COLUMN_NAME
   AND A.OWNER ='소유자넣기'
 ORDER BY A.TABLE_NAME, A.COLUMN_ID

;

 

 

 

// index 정보

SELECT OWNER,
       INDEX_NAME,
       UNIQUENESS,
       TABLESPACE_NAME,
       TABLE_NAME
  FROM ALL_INDEXES
 WHERE TABLE_OWNER  = :as_owner
   AND TABLE_NAME   = :as_table_name
 ORDER BY INDEX_NAME

 

 

// INDEX COLUMN정보

SELECT A.COLUMN_POSITION,
       A.COLUMN_NAME,
       B.DATA_TYPE,
       DECODE(B.DATA_TYPE,'NUMBER',DECODE(NVL(B.DATA_PRECISION,0),0,'',TO_CHAR(B.DATA_PRECISION)||','||B.DATA_SCALE),TO_CHAR(B.DATA_LENGTH))  DATA_LENGTH
  FROM ALL_IND_COLUMNS A,
       ALL_TAB_COLUMNS B
 WHERE A.INDEX_OWNER   = :as_owner
   AND A.INDEX_NAME    = :as_index_name
   AND B.OWNER         = :as_owner
   AND B.TABLE_NAME    = :as_table_name
   AND B.COLUMN_NAME   = A.COLUMN_NAME
 ORDER BY A.COLUMN_POSITION