테이블 정보(컬럼 정보, 코멘트 등등)

컬럼이 많은 테이블의 경우 코멘트로 컬럼명을 찾고 싶을떄가 있다

아래 쿼리를 돌리자.

SELECT * FROM (

SELECT 

  DECODE(column_id,0,TABLE_NAME,NULL) AS TABLE_NAME,

  TO_NUMBER(DECODE(column_id,0,NULL,column_id))  AS COLUMN_ID,

  COLUMN_NAME, NULLABLE, DATA_TYPE, COMMENTS

FROM (

  SELECT cc.TABLE_NAME,

    tc.COLUMN_ID, tc.COLUMN_NAME,

    tc.NULLABLE, tc.DATA_TYPE || 

      CASE WHEN tc.DATA_SCALE IS NOT NULL THEN '(' || tc.DATA_PRECISION || ',' || tc.DATA_SCALE || ')'

        WHEN tc.DATA_PRECISION IS NOT NULL THEN '(' || tc.DATA_PRECISION || ')'

        WHEN tc.DATA_LENGTH IS NOT NULL AND tc.DATA_TYPE LIKE '%CHAR%' THEN '(' || tc.DATA_LENGTH || ')'

      END DATA_TYPE,

    cc.COMMENTS

  FROM user_col_comments cc

  INNER JOIN user_tab_cols tc ON (cc.TABLE_NAME = tc.TABLE_NAME AND cc.TABLE_NAME = tc.TABLE_NAME AND cc.COLUMN_NAME = tc.COLUMN_NAME) 

  UNION

  SELECT tab.table_name AS TABLE_NAME, 

         0 AS COLUMN_ID, '' AS COLUMN_NAME,

         '' AS NULLABLE, '' AS DATA_TYPE,

         tab.comments AS COMMENTS

  FROM user_tab_comments tab

) 

WHERE table_name = UPPER(:MY_TABLE)

ORDER BY table_name, column_id

)

WHERE comments LIKE '%일련번호%'