오라클 테이블/컬럼 정보 조회

 

Oracle 테이블, 컬럼 속성 조회 쿼리이다.

반응형

'테이블 네임'에 테이블 명을 조건을 걸어주면,

테이블 영문, 한글명, 컬럼 속성, 컬럼 명, 컬럼 데이터 타입 등 가져오는 쿼리다.

 

728x90

SELECT TAB_COL.TABLE_NAME
	   , TAB_COMM.COMMENTS AS "DESC"
       , TAB_COL.COLUMN_NAME
       , TAB_COL.DATA_TYPE || ' (' || TAB_COL.DATA_LENGTH || ')' AS "TYPE"
       , COL_COL.COMMENTS
	   , CASE WHEN PK_INFO.POSITION IS NOT NULL THEN '●' ELSE '○' END AS PK
       , TAB_COL.NULLABLE AS "NN"
       , TAB_COL.COLUMN_ID AS "ID"
       , PK_INFO.POSITION
	   
  FROM ALL_TAB_COLUMNS TAB_COL
       INNER JOIN ALL_COL_COMMENTS COL_COL
               ON 1 = 1
                  AND TAB_COL.TABLE_NAME = '테이블 네임'
                  AND TAB_COL.TABLE_NAME = COL_COL.TABLE_NAME
                  AND TAB_COL.COLUMN_NAME = COL_COL.COLUMN_NAME
				  
       INNER JOIN ALL_TAB_COLUMNS TAB_COM
               ON 1 = 1
                  AND TAB_COM.TABLE_NAME = TAB_COL.TABLE_NAME
                  AND TAB_COM.TABLE_NAME = COL_COL.TABLE_NAME
                  AND TAB_COM.COLUMN_NAME = TAB_COL.COLUMN_NAME
                  AND TAB_COM.COLUMN_NAME = COL_COL.COLUMN_NAME
	   
	   INNER JOIN ALL_TAB_COMMENTS TAB_COMM
               ON 1 = 1
                  AND TAB_COMM.TABLE_TYPE = 'TABLE'
			      AND TAB_COMM.TABLE_NAME = TAB_COL.TABLE_NAME
				  AND TAB_COMM.TABLE_NAME = COL_COL.TABLE_NAME
				  AND TAB_COMM.OWNER = TAB_COL.OWNER
                  AND TAB_COMM.OWNER = COL_COL.OWNER
                  AND TAB_COL.OWNER = TAB_COM.OWNER
                  
       LEFT OUTER JOIN (SELECT B.TABLE_NAME
                               , B.COLUMN_NAME
                               , B.POSITION
                          FROM SYS.ALL_CONSTRAINTS A
                               INNER JOIN SYS.ALL_CONS_COLUMNS B
                                       ON 1 = 1
                                          AND A.CONSTRAINT_NAME =
                                              B.CONSTRAINT_NAME
                                          AND A.OWNER = B.OWNER
                                          AND A.TABLE_NAME = B.TABLE_NAME
                                          AND A.CONSTRAINT_TYPE = 'P'
                         ORDER BY B.POSITION) PK_INFO
                    ON 1 = 1
                       AND PK_INFO.TABLE_NAME = TAB_COL.TABLE_NAME
                       AND PK_INFO.TABLE_NAME = COL_COL.TABLE_NAME
                       AND PK_INFO.COLUMN_NAME = COL_COL.COLUMN_NAME
                       AND PK_INFO.COLUMN_NAME = TAB_COL.COLUMN_NAME
 ORDER BY TAB_COL.TABLE_NAME
          , TAB_COL.COLUMN_ID
;