테이블별로 컬럼이름,컬럼순서,null여부,column datatype, prec(정밀도),scale(스케일,소수점 이하표시), default 여부 fk, pk를 한번의 SQL로 확인할 수 있는 SQL이다.
아래는 출력되는 예시이다.
SELECT DISTINCT a.table_name,a.column_name,a.def_order,a.null여부,a.data_type,a.prec,a.scal,a.[default], NVL(b.key_attr_name,' ') AS PK, NVL(c.key_attr_name, ' ') AS FK
FROM
(
SELECT UPPER(a.class_name) AS TABLE_NAME
,upper(b.attr_name) AS COLUMN_NAME
,b.DEF_ORDER
,UPPER(b.is_nullable) AS NULL여부
,case WHEN b.data_type = 'STRING' THEN 'VARCHAR' ELSE b.data_type END DATA_TYPE
,UPPER(b.prec) AS PREC
,UPPER(b.scale) AS SCAL
,UPPER(NVL(b.default_value,' ')) AS [DEFAULT]
FROM db_class a, db_attribute b
WHERE a.class_name = b.class_name
AND a.owner_name <> 'DBA'
AND a.class_type = 'CLASS'
ORDER BY a.class_name,b.def_order
)a
LEFT OUTER JOIN
(
SELECT *
FROM db_index_key
WHERE index_name IN
( SELECT index_name
FROM db_index
where is_primary_key = 'YES')
) b
ON A.TABLE_NAME = UPPER(B.CLASS_NAME)
AND a.COLUMN_NAME = UPPER(b.key_attr_name)
LEFT OUTER JOIN
(
SELECT *
FROM db_index_key
WHERE index_name IN
( SELECT index_name
FROM db_index
where is_foreign_key = 'YES')
) C
ON A.TABLE_NAME = UPPER(C.CLASS_NAME)
AND A.COLUMN_NAME = UPPER(C.key_attr_name)
ORDER BY table_name, a.def_order