가끔씩 테이블 전체의 정보를 한방에 가져 오고 싶을때가 있죠... (문서화및... 기타 의 경우)
아래의 쿼리를 이용하면 Okay
- 테이블 정보 조회
SELECT tbl.TABLE_NAME, comments.COMMENTS, tbl.TABLESPACE_NAME
FROM USER_TABLES tbl, USER_TAB_COMMENTS comments
WHERE tbl.TABLE_NAME = comments.TABLE_NAME
ORDER BY tbl.TABLE_NAME
- 컬럼까지 조회
SELECT tab_columns.TABLE_NAME,
tab_columns.COLUMN_ID,
tab_columns.COLUMN_NAME,
(case
when DATA_TYPE like '%CHAR%' then DATA_TYPE || '(' || DATA_LENGTH || ')'
when DATA_TYPE = 'NUMBER' and DATA_PRECISION > 0 and DATA_SCALE > 0 then DATA_TYPE || '(' || DATA_PRECISION || ',' || DATA_SCALE || ')'
when DATA_TYPE = 'NUMBER' and DATA_PRECISION > 0 then DATA_TYPE || '(' || DATA_PRECISION || ')'
when DATA_TYPE = 'NUMBER' then DATA_TYPE
else DATA_TYPE
end) DATA_TYPE,
decode(NULLABLE, 'N', 'Not Null', 'Null') NULLABLE,
DATA_DEFAULT,
(
SELECT decode(
sum((
SELECT decode(CONSTRAINT_TYPE, 'P', 1, 'R', 2, 0)
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = cons_columns.CONSTRAINT_NAME
))
, 1, 'PK', 2, 'FK', 3, 'PK, FK', '')
FROM USER_CONS_COLUMNS cons_columns
WHERE TABLE_NAME = tab_columns.TABLE_NAME AND COLUMN_NAME = tab_columns.COLUMN_NAME
) CONSTRAINTS,
comments.COMMENTS
FROM USER_TAB_COLUMNS tab_columns, USER_COL_COMMENTS comments
WHERE tab_columns.TABLE_NAME = comments.TABLE_NAME(+) AND tab_columns.COLUMN_NAME = comments.COLUMN_NAME(+)
-- AND tab_columns.TABLE_NAME = 'TLXR'
ORDER BY tab_columns.TABLE_NAME, COLUMN_ID