잘되는군...ㅋㅋㅋㅋㅋㅋㅋ
SELECT owner,
(SELECT TRIM (comments)
FROM all_tab_comments
WHERE owner = a.owner AND table_name = a.table_name)
AS tab_comment,
a.table_name, a.column_name AS col_name,
DECODE (a.data_type,
'DATE', 'DATE',
'NUMBER', 'NUMBER('
|| data_length
|| DECODE (data_precision, NULL, '', ',' || data_precision)
|| ')',
a.data_type || '(' || data_length || ')'
) AS data_type,
data_length AS data_len,
DECODE (a.nullable, 'N', 'NOT NULL', 'NULL') AS nullable,
DECODE (a.column_name,
(SELECT z.column_name
FROM dba_olap_key_column_uses z, dba_olap_keys x
WHERE x.owner = z.owner
AND x.owner = a.owner
AND x.table_name = z.table_name
AND x.table_name = a.table_name
AND x.key_name = z.key_name
AND z.column_name = a.column_name), 'Yes',
'No'
) AS pk,
DECODE (a.column_name,
(SELECT z.column_name
FROM dba_olap_key_column_uses z, dba_olap_keys x
WHERE x.owner = z.owner
AND x.owner = a.owner
AND x.table_name = z.table_name
AND x.table_name = a.table_name
AND x.key_name <> z.key_name
AND z.column_name = a.column_name), 'Yes',
'No'
) AS fk,
(SELECT TRIM (comments)
FROM all_col_comments
WHERE owner = a.owner
AND table_name = a.table_name
AND column_name = a.column_name) AS col_comment,
' ' AS data_default, internal_column_id AS col_id,
TO_CHAR (SYSDATE, 'yyyy-mm-dd') AS cur_day
FROM all_tab_cols a
WHERE a.owner = 'LMS' AND a.table_name = 'TLD_PAYINST'
ORDER BY a.table_name, col_id, pk, a.column_name
'database' 카테고리의 다른 글
| Never Use a RETURN Statement Inside a Loop (0) | 2006.12.26 |
|---|---|
| EXECUTE IMMEDIATE (0) | 2006.12.16 |
| SQL%ROWCOUNT (0) | 2006.11.28 |
| 순위(RANK)관련 오라클 함수 (0) | 2006.10.25 |
| excel을 이용하여 테이블에 insert하는 stored procedure (0) | 2006.05.31 |