메타테이블 정보 조회(오라클)

# 메타테이블 정보 조회

• 테이블명, 컬럼명, 컬럼타입, 컬럼길이, 컬럼코멘트, PK여부, NULL 여부 등의 정보를 가져오는 쿼리.

SELECT distinct A.TABLE_NAME , A.COLUMN_NAME , A.DATA_TYPE , A.DATA_LENGTH , B.COMMENTS , C.PK , A.NULLABLE FROM DBA_TAB_COLUMNS A , DBA_COL_COMMENTS B , (SELECT T.OWNER , T.TABLE_NAME , T.CONSTRAINT_TYPE , COLUMN_NAME , CASE WHEN T.CONSTRAINT_TYPE = 'P' THEN 'Y' END AS PK FROM DBA_CONS_COLUMNS S , DBA_CONSTRAINTS T WHERE T.OWNER = S.OWNER AND T,OWNER = 'DB 아이디명' AND T.TABLE_NAME = '테이블명' AND T.TABLE_NAME = S.TABLE_NAME AND T.CONSTRAINT_NAME = S.CONSTRAINT+NAME AND T.CONSTRAINT_TYPE = 'P') C WHERE A.OWNER = 'DB 아이디명' AND A.TABLE_NAME = '테이블명' AND A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND A.OWNER = C.OWNER(+) AND A.TABLE_NAME = C.TABLE_NAME(+) AND A.COLUMN_NAME = C.COLUMN_NAME(+)

# 테이블 정보 조회(개선)

• 번호, 순번, 엔티티명추가

SELECT DENSE_RANK()OVER(ORDER BY A.TABLE_NAME) AS NO
	, A.TABLE_NAME
	, ROW_NUMBER()OVER(PARTITION BY A.TABLE_NAME ORDER BY NULL) AS COL_NO
	, C.COMMENTS AS ENTITY_NAME
	, A.COLUMN_NAME
	, A.DATA_TYPE
	, A.DATA_LENGTH
	, B.COMMENTS
	, NVL2(T.CONSTRAINT_TYPE, T.CONSTRAINT_TYPE, '') AS PK
	, A.NULLABLE
FROM DBA_TAB_COLUMNS A LEFT JOIN DBA_CONS_COLUMNS S
ON A.COLUMN_NAME = S.COLUMN_NAME AND A.TABLE_NAME = S.TABLE_NAME AND A.OWNER = S.OWNER AND S.POSITION > 0 AND INSTR(S.CONSTRAINT_NAME, 'PK') != 0
LEFT JOIN DBA_CONSTRAINTS T
ON S.CONSTRAINT_NAME = T.CONSTRAINT_NAME AND S.OWNER = T.OWNER AND S.TABLE_NAME = T.TABLE_NAME
, DBA_COL_COMMENTS B
, ALL_TAB_COMMENTS C
WHERE A.OWNER = 'DB명'
AND A.TABLE_NAME In ( '테이블명A','테이블명B', . . .)
AND B.OWNER = A.OWNER
AND B.TABLE_NAME = A.TABLE_NAME
AND B.COLUMN_NAME = A.COLUMN_NAME
AND C.OWNER = A.OWNER
AND C.TABLE_NAME = A.TABLE_NAME

# 테이블 정보 조회(추가개선)

• DBA_TABLES를 주 테이블로 사용, ANSI(표준)쿼리 사용

SELECT DENSE_RANK()OVER(ORDER BY A.TABLE_NAME) AS NO
	, A.TABLE_NAME
    	, ROW_NUMBER()OVER(PARTITION BY A.TABLE_NAME ORDER BY NULL) AS COL_NO
    	, C.COMMENTS AS ENTITY_NAME
	, A.COLUMN_NAME
	, A.DATA_TYPE
	, A.DATA_LENGTH
	, B.COMMENTS
	, NVL2(T.CONSTRAINT_TYPE, T.CONSTRAINT_TYPE, '') AS PK
	, A.NULLABLE
FROM DBA_TABLES M LEFT JOIN DBA_TAB_COLUMS A
ON A.OWNER = M.OWNER AND A.TABLE_NAME = M.TABLE_NAME
LEFT JOIN DBA_COL_COMMENTS B
ON B.OWNER = M.OWNER AND B.TABLE_NAME = M.TABLE_NAME AND B.COLUMN_NAME = M.COLUMN_NAME
LEFT JOIN DBA_TAB_COMMENTS C
ON C.OWNER = M.OWNER AND C.TABLE_NAME = M.TABLE_NAME
LEFT JOIN DBA_CONS_COLUMNS S
ON S.COLUMN_NAME = A.COLUMN_NAME AND S.TABLE_NAME = M.TABLE_NAME AND S.OWNER = M.OWNER AND S.POSITION > 0 AND INSTR(S.CONSTRAINT_NAME, 'PK') != 0
LEFT JOIN DBA_CONSTRAINTS T
ON T.CONSTRAINT_NAME = S.CONSTRAINT_NAME AND T.OWNER = M.OWNER AND T.TABLE_NAME = M.TABLE_NAME
WHERE M.OWNER = 'DB명'
AND M.TABLE_NAME IN ( '테이븖명A', '테이블명B', ...)