데이터베이스 질문과 답변
DBA 입장에서 첨부파일 내용과 같이 컬럼 정의서를 추출하려고 하는데
SELECT A.OWNER AS "계정ID",
A.TABLE_NAME AS "테이블영문명",
C.COMMENTS AS "테이블한글명",
A.COLUMN_NAME AS "컬럼영문명",
B.COMMENTS AS "컬럼한글명",
A.DATA_TYPE AS "데이터타입",
A.DATA_LENGTH AS "데이터길이"
FROM DBA_TAB_COLUMNS A
INNER JOIN DBA_COL_COMMENTS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
INNER JOIN DBA_TAB_COMMENTS C
ON A.TABLE_NAME = C.TABLE_NAME
WHERE OWNER = "USR_STT" -- OWNER명
ORDER BY A.TABLE_NAME
수정해야 하는 부분 조언해 주세요..
그리고 WHERE 절에서 OWNER = 'USR_STT' 뒤에 OR 절로 여러개의 계정에서 한번에 추출 가능한지도 궁금합니다
1. WHERE 절 오류 : 테이블 알리아스 미지정, 따옴표 오류
- 오류 : WHERE owner = "USR_STT"
- 수정 : WHERE a.owner = 'USR_STT'
2. 조인 조건 누락
- owner 항목 조건 필요
3. 정렬 기준 추가 필요
- ORDER BY a.owner, a.table_name, a.column_id
4. 데이터 길이
- 데이터타입에 따라 사용 컬럼이 다름
5. 추가 항목 검토
- PK 여부, NULL 여부 등
6. 여러 사용자 지정
- WHERE a.owner IN ('SCOTT', 'HR')
ORDER BY a.owner, a.table_name, a.column_id
PK 여부 추가는 구글에 많은 쿼리가 있지만 현재 제가 쿼리를 완벽히 이해하긴 어려워서 누락하였습니다.
4. 데이터 타입에 따라 사용 컬럼이 다름 이라고 말씀해주셨는데
이 부분도 이해하지 못 했습니다. 그 외 부분 수정하여 올립니다.
SELECT A.OWNER AS "계정ID",
A.TABLE_NAME AS "테이블영문명",
C.COMMENTS AS "테이블한글명",
A.COLUMN_NAME AS "컬럼영문명",
B.COMMENTS AS "컬럼한글명",
A.DATA_TYPE AS "데이터타입",
A.DATA_LENGTH AS "데이터길이“,
A.NULLABLE AS "Null 여부"
FROM DBA_TAB_COLUMNS A
INNER JOIN DBA_COL_COMMENTS B
ON A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
INNER JOIN DBA_TAB_COMMENTS C
ON A.OWNER = C.OWNER
AND A.TABLE_NAME = C.TABLE_NAME
WHERE A.OWNER IN (‘USR_STT’, ‘USR_ATS’)
ORDER BY A.TABLE_NAME, A.COLUMN_ID
수정한 부분에 다른 문제가 있을까요
멋진 답변 항상 감사드립니다..
NUMBER 타입의 경우 NUMBER(data_precision, data_scale) 형태로 표현합니다. 예) NUMBER(4, 2)
DATE 타입의 경우 굳이 사이즈를 표현할 필요가 있을지?
VARCHAR2 의 경우 char_length 와 char_used 를 혼합하여 표현합니다. VARCHAR2(1 BYTE), VARCHAR2(1 CHAR)
틀린값이 나오니 오류가 맞네요 ... 그래서 CASE 문을 이용해서 DATA_TYPE 별로 다른 형식으로 받아오게 해봤습니다.
SELECT A.OWNER AS "계정ID",
A.TABLE_NAME AS "테이블영문명",
C.COMMENTS AS "테이블한글명",
A.COLUMN_NAME AS "컬럼영문명",
B.COMMENTS AS "컬럼한글명",
A.DATA_TYPE AS "데이터타입",
A.DATA_LENGTH (CASE A.DATA_TYPE
WHEN 'NUMBER' THEN '(' || TO_CHAR(A.DATA_PRECISION) || ',' || TO_CHAR(A.DATA_SCALE) || ')'
WHEN 'DATE' THEN ' '
ELSE '(' || A1.DATA_LENGTH || ')'
END) AS "데이터길이"
FROM DBA_TAB_COLUMNS A
INNER JOIN DBA_COL_COMMENTS B
ON A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
INNER JOIN DBA_TAB_COMMENTS C
ON A.OWNER = C.OWNER
AND A.TABLE_NAME = C.TABLE_NAME
WHERE A.OWNER IN ('USR_STT')
ORDER BY A.OWNER, A.TABLE_NAME, A.COLUMN_ID
답변 감사합니다.
DATA_LENGTH를 조회하면 NUMBER(4, 2)는 실제 데이터가 172이면 3으로 표현되나요?
VARCHAR2 의 경우는 BYTE와 CHAR가 구분되지 않고 길이만 나오겠군요..
데이터 타입 별로 DATA_LENGTH 받는 형식을 구분해줘야 정확하겠네요.
그러나 위의 쿼리문을 실행해도 형식을 정확하게 구분하진 못 하지만 DATA_LENGTH는 오류 없이 나오겠죠?..
172 는 NUMBER(4, 2) 에 입력 못합니다. 전체 4자리중 소수 2자리이므로 정수부는 2자리입니다.
NUMBER 타입의 DATA_LENGTH 는 일률적으로 22 로 표현되네요.
DATA_LENGTH는 오류 없이 나오겠죠? --> 틀린 값이 나오니 오류 아닌지요?
SELECT a.owner AS "계정ID"
, a.table_name AS "테이블영문명"
, c.comments AS "테이블한글명"
, a.column_name AS "컬럼영문명"
, b.comments AS "컬럼한글명"
, a.data_type AS "데이터타입"
, a.data_length
, CASE WHEN a.data_type LIKE '%CHAR%' THEN
a.char_length || DECODE(a.char_used, 'C', ' CHAR')
WHEN a.data_type = 'NUMBER' AND a.data_precision > 0 THEN
a.data_precision || DECODE(a.data_scale, 0, '', ', '||a.data_scale)
END "데이터길이"
, e.column_position AS "PK"
, a.nullable
, a.column_id
FROM dba_tab_columns a
INNER JOIN dba_col_comments b
ON a.owner = b.owner
AND a.table_name = b.table_name
AND a.column_name = b.column_name
INNER JOIN dba_tab_comments c
ON a.owner = c.owner
AND a.table_name = c.table_name
-- PK Start --
LEFT OUTER JOIN dba_constraints d
ON a.owner = d.owner
AND a.table_name = d.table_name
AND d.constraint_type = 'P'
LEFT OUTER JOIN dba_ind_columns e
ON d.index_owner = e.index_owner
AND d.index_name = e.index_name
AND a.column_name = e.column_name
-- PK End --
WHERE a.owner IN ('SCOTT')
ORDER BY a.table_name, a.column_id
;
) 버튼을 클릭하여 작성 하시면 됩니다.