[Oracle] 테이블 정의서(코멘트, 타입, PK, NULL여부, 디폴트 값) 조회

쿼리

SELECT A.COLUMN_NAME
	 , A.COMMENTS
	 , B.DATA_TYPE || '(' || B.DATA_LENGTH || ')'  AS DATA_TYPE
	 , B.NULLABLE
	 , B.DATA_DEFAULT
FROM   ALL_COL_COMMENTS A, USER_TAB_COLUMNS B
WHERE  A.TABLE_NAME  = '테이블명'
AND    A.TABLE_NAME  = B.TABLE_NAME
AND    A.COLUMN_NAME = B.COLUMN_NAME
ORDER  BY B.COLUMN_ID

 

COLUMN_NAME	COMMENTS	DATA_TYPE	NULLABLE	DATA_DEFAULT
EMPNO	사원번호	VARCHAR2(20)	N	('001')
EMPNAME	사원이름	VARCHAR2(50)	N

 

 

PK 정보 포함해서 조회하기

WITH PK_INFO AS (
	SELECT A.TABLE_NAME
	     , B.COLUMN_NAME
	     , 'PK' AS PK
	FROM ALL_CONSTRAINTS  A
	     , ALL_CONS_COLUMNS B
	WHERE A.TABLE_NAME      = '테이블이름'
	AND A.CONSTRAINT_TYPE = 'P' /*PK만 조회*/
	AND A.OWNER           = B.OWNER
	AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
	ORDER BY B.POSITION
)


SELECT A.COLUMN_NAME
	 , A.COMMENTS
	 , B.DATA_TYPE || '(' || B.DATA_LENGTH || ')'  AS DATA_TYPE
	 , B.NULLABLE
     , C.PK
	 , B.DATA_DEFAULT
FROM   ALL_COL_COMMENTS A, USER_TAB_COLUMNS B
     , PK_INFO C
WHERE  A.TABLE_NAME  = '테이블이름'
AND    A.TABLE_NAME  = B.TABLE_NAME
AND    A.COLUMN_NAME = B.COLUMN_NAME
AND    B.COLUMN_NAME = C.COLUMN_NAME(+)
ORDER  BY B.COLUMN_ID

 

COLUMN_NAME	COMMENTS	DATA_TYPE	NULLABLE	PK	DATA_DEFAULT
EMPNO	사원번호	VARCHAR2(20)	N	PK	('001')
EMPNAME	사원이름	VARCHAR2(50)	N