오라클 스키마 정보 수집

오라클 DB에서 해당 DB에 스키마정보를 수집하는 쿼리이다. 
참고합시다~

/*-------------------------------------------------------------------
	오라클 스키마 자료 수집
-------------------------------------------------------------------*/

SELECT D.OWNER
     , D.TABLE_NAME
	 , A.COMMENTS AS TBL_COMMENTS
	 , D.COLUMN_ID
	 , D.COLUMN_NAME
	 , B.COMMENTS AS COL_COMMENTS
	 , C.IS_PK
	 , CASE WHEN D.DATA_TYPE IN ('VARCHAR2','NVARCHAR2')                                      THEN D.DATA_TYPE || '(' || D.DATA_LENGTH || ')'
	        WHEN D.DATA_TYPE = 'NUMBER' AND D.DATA_PRECISION IS NULL AND D.DATA_SCALE IS NULL THEN 'NUMBER'
			WHEN D.DATA_TYPE = 'NUMBER' AND D.DATA_PRECISION > 0 AND D.DATA_SCALE = 0         THEN 'NUMBER' || '(' || D.DATA_PRECISION || ')'
			WHEN D.DATA_TYPE = 'NUMBER' AND D.DATA_PRECISION > 0 AND D.DATA_SCALE > 0         THEN 'NUMBER' || '(' || D.DATA_PRECISION || ','|| D.DATA_SCALE ||')'
			ELSE D.DATA_TYPE END AS DATA_TYPE
     , D.DATA_TYPE
     , D.DATA_LENGTH
     , D.DATA_SCALE
     , D.NULLABLE
     , D.DATA_DEFAULT
     , D.NUM_DISTINCT
     , D.LOW_VALUE
 	 , D.HIGH_VALUE 
  FROM DBA_TAB_COLUMNS D
     , DBA_TABLES E
	 , (
	    SELECT B.OWNER
		     , B.TABLE_NAME
			 , B.COLUMN_NAME
			 , 'Y' IS_PK
		  FROM DBA_CONSTRAINTS  C
		     , DBA_CONS_COLUMNS B
	     WHERE 1=1
		   AND C.OWNER = B.OWNER
		   AND C.CONSTRAINT_NAME = B.CONSTRAINT_NAME
		   AND C.CONSTRAINT_TYPE = 'P'
	   ) C
	 , DBA_TAB_COMMENTS A
	 , DBA_COL_COMMENTS B
 WHERE 1=1
   AND D.OWNER = C.OWNER(+)
   AND D.OWNER = E.OWNER
   AND D.TABLE_NAME = E.TABLE_NAME
   AND D.TABLE_NAME = C.TABLE_NAME(+)
   AND D.COLUMN_NAME = C.COLUMN_NAME(+)
   AND D.OWNER = A.OWNER(+)
   AND D.TABLE_NAME = A.TABLE_NAME(+)
   AND D.OWNER = B.OWNER(+)
   AND D.TABLE_NAME = B.TABLE_NAME(+)
   AND D.COLUMN_NAME = B.COLUMN_NAME(+)