테이블 정의서 만들때 적당히 수정해서 쓰면 되겠다.
-- dba_tab_columns 조회 가능할 때
SELECT a1.table_comments, a1.table_name, a1.column_id, a1.column_name, a1.data_type, a1.data_length, a1.nullable
, DECODE (b1.constraint_type, 'P', 'PK', 'U', 'UK', 'C', 'C', 'R', 'FK') constraint_type
, a1.column_comments, a1.data_default
FROM (SELECT b.comments table_comments, a.owner, a.table_name, a.column_id, a.column_name, c.comments column_comments
, a.nullable, a.data_type, a.data_default
, CASE WHEN a.data_type = 'DATE'
THEN NULL
WHEN a.data_type = 'NUMBER' AND a.data_precision IS NULL
THEN NULL
WHEN a.data_type = 'NUMBER'
THEN a.data_precision || ',' || a.data_scale
ELSE a.data_length || ''
END data_length
FROM dba_tab_columns a, dba_tab_comments b, dba_col_comments c
WHERE a.owner = 'OWNER'
AND a.table_name = 'TABLE_NAME'
AND a.owner = b.owner
AND a.table_name = b.table_name
AND a.owner = c.owner
AND a.table_name = c.table_name
AND a.column_name = c.column_name) a1,
(SELECT a.table_owner, a.table_name, a.column_name, b.constraint_type
FROM dba_ind_columns a, dba_constraints b
WHERE a.table_owner = 'OWNER'
AND a.table_name = 'TABLE_NAME'
AND a.table_owner = b.owner
AND a.index_name = b.constraint_name) b1
WHERE a1.owner = b1.table_owner(+)
AND a1.table_name = b1.table_name(+)
AND a1.column_name = b1.column_name(+)
ORDER BY 1,2,3
;
-- dba_tab_columns 조회 불가능할 때
SELECT a1.table_comments, a1.table_name, a1.column_id, a1.column_name, a1.data_type, a1.data_length, a1.nullable
, DECODE (b1.constraint_type, 'P', 'PK', 'U', 'UK', 'C', 'C', 'R', 'FK') constraint_type
, a1.column_comments, a1.data_default
FROM (SELECT b.comments table_comments, a.table_name, a.column_id, a.column_name, c.comments column_comments
, a.nullable, a.data_type, a.data_default
, CASE WHEN a.data_type = 'DATE'
THEN NULL
WHEN a.data_type = 'NUMBER' AND a.data_precision IS NULL
THEN NULL
WHEN a.data_type = 'NUMBER'
THEN a.data_precision || ',' || a.data_scale
ELSE a.data_length || ''
END data_length
FROM user_tab_columns a, user_tab_comments b, user_col_comments c
WHERE a.table_name = 'TABLE_NAME'
AND a.table_name = b.table_name
AND a.table_name = c.table_name
AND a.column_name = c.column_name
AND EXISTS (SELECT 1 FROM user_tables WHERE table_name = a.table_name)) a1,
(SELECT a.table_name, a.column_name, b.constraint_type
FROM user_ind_columns a, user_constraints b
WHERE a.table_name = 'TABLE_NAME'
AND a.index_name = b.constraint_name) b1
WHERE a1.table_name = b1.table_name(+)
AND a1.column_name = b1.column_name(+)
ORDER BY 1,2,3
;
- 참고 URL -
http://blog.naver.com/co_ke1213?Redirect=Log&logNo=150023722522
http://ooya.egloos.com/970761
SELECT a1.table_comments, a1.table_name, a1.column_comments, a1.column_name
, (CASE WHEN b1.constraint_type = 'P' THEN 'Y' END) pk_flag
, (CASE WHEN b1.constraint_type = 'R' THEN 'Y' END) fk_flag
, a1.null_flag, a1.data_type, a1.data_length
FROM (SELECT b.comments table_comments, a.table_name, c.comments column_comments, a.column_name
, (CASE a.nullable WHEN 'Y' THEN 'Y' END) null_flag, a.data_type
, (CASE
WHEN a.data_type IN ('CHAR', 'VARCHAR2')
THEN '(' || a.data_length || ')'
WHEN a.data_type = 'NUMBER' AND a.data_scale = 0 AND a.data_precision IS NOT NULL
THEN '(' || a.data_precision || ')'
WHEN a.data_type = 'NUMBER' AND a.data_scale <> 0
THEN '(' || a.data_precision || ',' || a.data_scale || ')'
END
) data_length
FROM user_tab_columns a, user_tab_comments b, user_col_comments c
WHERE (a.table_name = b.table_name)
AND (a.table_name = c.table_name
AND a.column_name = c.column_name)) a1,
(SELECT a.table_name, a.column_name, b.constraint_type
FROM user_cons_columns a, user_constraints b
WHERE (a.constraint_name = b.constraint_name)
AND b.constraint_type IN ('P', 'R')) b1
WHERE a1.table_name = b1.table_name(+)
AND a1.column_name = b1.column_name(+)
AND a1.table_name IN (SELECT table_name FROM user_tables)
;