「Query」컬럼 정보 쿼리

컬럼 정보 쿼리

SELECT A.OWNER, A.TABLE_NAME, replace(replace(C.COMMENTS, chr(10), ' '), chr(13), ' ') TABLE_COMMENTS

     , A.COLUMN_ID, A.COLUMN_NAME, replace(replace(B.COMMENTS, chr(10), ' '), chr(13), ' ') COLUMNS_COMMENTS, A.DATA_TYPE, A.DATA_LENGTH, A.NULLABLE

     , NVL2(d.column_name,'PK','') Pri

     ,A.*, B.* 

FROM ALL_TAB_COLUMNS a, ALL_COL_COMMENTS b, ALL_TAB_COMMENTS C, 

    (SELECT cols.table_name, cols.column_name, cols.owner

       FROM all_constraints cons, all_cons_columns cols

      WHERE cols.table_name = :TABLE_NAME

        AND cons.constraint_type = 'P'

        AND cons.constraint_name = cols.constraint_name

        AND cons.owner = cols.owner

        AND cons.OWNER = :OWNER) d

WHERE a.OWNER = B.OWNER

  AND A.TABLE_NAME = B.TABLE_NAME 

  AND A.COLUMN_NAME = B.COLUMN_NAME 

  AND a.OWNER = C.OWNER

  AND A.TABLE_NAME = C.TABLE_NAME 

  AND a.TABLE_NAME  = d.TABLE_NAME (+)

  AND a.OWNER = d.OWNER (+) 

  AND a.COLUMN_NAME = d.column_name(+)

  AND A.OWNER = :OWNER 

   AND A.TABLE_NAME = :TABLE_NAME   

 ORDER BY A.COLUMN_ID ;