오라클 테이블, 컬럼, pk, comment, data type 조회

SELECT
	'EDW' AS 시스템명
	,T3.OWNER
	,T3.TABLE_COMMENT 	AS 엔티티명
	,TC1.TABLE_NAME	 	AS 테이블명
	,T3.COLUMNS_COMMENTS AS 속성명
	,TC1.COLUMN_NAME	AS 컬럼명
	,TC1.DATA_TYPE AS DATA_TYPE
	,CASE WHEN TC1.DATA_TYPE ='NUMBER' THEN TO_NUMBER(TC1.DATA_LENGTH||'.'||TC1.DATA_SCALE)
	ELSE TC1.DATA_LENGTH END AS LENGTH
	,T2.PK_YN			AS PK
--	,NULL AS FK_YN

--	,TC1.DATA_LENGTH AS LENG
--	,TC1.DATA_SCALE AS SCAL
	,CASE WHEN TC1.NULLABLE='N' THEN 'Y'
	  WHEN TC1.NULLABLE='Y' THEN ''
	  ELSE NULL
	 END AS NOT_NULL
	,TC1.COLUMN_ID
FROM
	/* PK컬럼여부*/
	USER_TAB_COLUMNS TC1
	LEFT OUTER JOIN (
	/* PK컬럼여부 */
		SELECT A.TABLE_NAME, B.COLUMN_NAME, CASE WHEN constraint_typE='P' THEN 'Y' END AS PK_YN
		FROM user_CONSTRAINTS A
		INNER JOIN user_CONS_COLUMNS B
		ON a.owner=b.owner and A.TABLE_NAME=B.TABLE_NAME and a.constraint_name=b.constraint_name
		where constraint_type IN ('P', 'U')
		--ORDER BY A.TABLE_NAME, B.COLUMN_NAME
	) T2
	ON TC1.TABLE_NAME=T2.TABLE_NAME AND TC1.COLUMN_NAME=T2.COLUMN_NAME
	LEFT OUTER JOIN (
	/*컬럼코멘트*/
		select
			T1.OWNER
			,T1.TABLE_NAME AS TABLE_ID
			,T1.COMMENTS AS TABLE_COMMENT
			,T2.COLUMN_NAME
			,T2.COMMENTS AS COLUMNS_COMMENTS
		FROM all_tab_comments T1
		INNER JOIN ALL_COL_COMMENTS T2
		ON T1.table_name=T2.table_name
	) T3
ON TC1.TABLE_NAME=T3.TABLE_ID AND TC1.COLUMN_NAME=T3.COLUMN_NAME
WHERE T3.OWNER='EDW' AND TC1.TABLE_NAME IN ( 'TABLE_NAME')
ORDER BY TC1.TABLE_NAME, TC1.COLUMN_ID;