컬럼정보조회

SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'TB_USER_INFO%' AND OWNER = 'SCOTT'

 

  SELECT T.TABLE_NAME AS TBL_NM

             , T.COLUMN_NAME AS COLS_ID              

             , C.COMMENTS AS COLS_NM

             , C.COMMENTS AS NOTE

             , CASE WHEN T.DATA_TYPE='TIMESTAMP(6)' THEN 'TIMESTAMP'

                    ELSE T.DATA_TYPE

               END AS DATA_TP_NM

             , CASE WHEN T.DATA_TYPE='VARCHAR' THEN T.DATA_LENGTH

                    WHEN T.DATA_TYPE='NUMBER' THEN T.DATA_PRECISION

               END AS DATA_LEN_VAL

             , T.NULLABLE AS NULL_YN

             , T.DATA_DEFAULT AS DFLT_VAL

             , DECODE((SELECT DECODE(C.COLUMN_NAME, '', '', 'Yes')  

                         FROM USER_CONS_COLUMNS C, USER_CONSTRAINTS S                   

                        WHERE C.CONSTRAINT_NAME = S.CONSTRAINT_NAME                     

                          AND S.CONSTRAINT_TYPE = 'P'                      

                          AND C.TABLE_NAME = T.TABLE_NAME           

                          AND C.COLUMN_NAME = T.COLUMN_NAME

                      ), 'Yes','Y','N')    AS PK_YN

          FROM USER_TAB_COLUMNS T, USER_COL_COMMENTS C                       

         WHERE T.TABLE_NAME  = C.TABLE_NAME           

           AND T.COLUMN_NAME = C.COLUMN_NAME      

           AND T.TABLE_NAME  = 'TB_USER_INFO'

         ORDER BY T.TABLE_NAME, T.COLUMN_ID ASC