테이블 정보 조회

/********************************************************************/
/*INPUT : table명 (대소문자 구분 없음 - 2자 이상) */
/********************************************************************/
-- table별 column 정보 조회
SELECT c.comments 엔티티명,
a.table_name 테이블명,
a.column_id 컬럼id,
b.comments 컬럼한글명,
a.column_name 컬럼영문명,
case a.data_type
when 'NUMBER' then a.data_type || '(' || a.data_precision ||',' || a.data_scale|| ')'
else a.data_type || '(' || a.data_length|| ')'
end as datatype,
decode(a.column_name,d.column_name,'PK','') PK여부,
decode(a.nullable,'N','NOT NULL','') NULL여부 ,
data_default Default값
FROM all_tab_columns a, all_col_comments b, all_tab_comments c,
(SELECT x.owner, x.table_name, y.position, y.column_name
FROM ALL_CONSTRAINTS X, ALL_CONS_COLUMNS Y
WHERE x.constraint_type = 'P'
AND x.owner like 'ERP%'-- 사용자
AND y.table_name = x.table_name
AND y.constraint_name = x.constraint_name
AND Y.OWNER = X.OWNER
order by 2,3 ) d
WHERE a.owner like 'E%' -- 사용자
AND a.table_name like upper('&table_name') || '%'
AND length(a.table_name) = 8 -- 테이블 크기
AND b.table_name = a.table_name
AND b.column_name = a.column_name
AND c.table_name = a.table_name
AND A.OWNER = B.OWNER
AND A.OWNER = C.OWNER
AND A.OWNER = D.OWNER(+)
AND A.OWNER = 'ERP' -- 사용자명
AND d.table_name (+) = a.table_name
AND d.column_name (+) = a.column_name
ORDER BY 테이블명, 컬럼ID ;