[ PL/SQL DEVELOPER에서 TABLE 전체 LAYOUT을 만드는 방법 - HTML 이용 ]
1. 아래의 쿼리를 PL/SQL Developer에서 실행한다
WITH TAB_LIST (TABLE_NAME, T_COMMENTS, TABLESPACE_NAME, COLUMN_ID, COLUMN_NAME, DATA_TYPE, C_KEY, NOTNULL, DATA_DEFAULT, C_COMMENTS)
AS(
select TBL.TABLE_NAME,
TCM.COMMENTS AS T_COMMENTS,
TBL.TABLESPACE_NAME,
TCL.COLUMN_ID,
TCL.COLUMN_NAME,
case
when TCL.DATA_TYPE = 'VARCHAR2' or TCL.DATA_TYPE = 'CHAR' or
TCL.DATA_TYPE = 'NUMBER' then
TCL.DATA_TYPE || '(' || DATA_LENGTH || ')'
else
TCL.DATA_TYPE
end as DATA_TYPE,
CON.KEY AS C_KEY,
decode(NULLABLE, 'N', 'NOT NULL', '') AS NOTNULL,
'' AS DATA_DEFALUT,
CCM.COMMENTS AS C_COMMENTS
from USER_TABLES TBL,
USER_TAB_COMMENTS TCM,
USER_TAB_COLUMNS TCL,
USER_COL_COMMENTS CCM,
(select CCL.TABLE_NAME,
COLUMN_NAME,
case
when sum(decode(CONSTRAINT_TYPE, 'P', 1, 0)) > 0 and
sum(decode(CONSTRAINT_TYPE, 'F', 1, 0)) > 0 then
'PK,FK'
when sum(decode(CONSTRAINT_TYPE, 'P', 1, 0)) > 0 then
'PK'
when sum(decode(CONSTRAINT_TYPE, 'F', 1, 0)) > 0 then
'FK'
else
''
end as KEY,
sum(decode(CONSTRAINT_TYPE, 'C', 0, 'P', 0, 'F', 0, 1)) as CCC
from USER_CONS_COLUMNS CCL, USER_CONSTRAINTS CNS
where CCL.CONSTRAINT_NAME = CNS.CONSTRAINT_NAME
group by CCL.TABLE_NAME, COLUMN_NAME) CON
where TBL.TABLE_NAME = TCM.TABLE_NAME
and TBL.TABLE_NAME = TCL.TABLE_NAME
and TCL.TABLE_NAME = CCM.TABLE_NAME
and TCL.COLUMN_NAME = CCM.COLUMN_NAME
and TCL.TABLE_NAME = CON.TABLE_NAME(+)
and TCL.COLUMN_NAME = CON.COLUMN_NAME(+)
-- AND TBL.TABLE_NAME LIKE 'TA_AP1%'
and TBL.TABLE_NAME = 'TB_SW23210'
order by TBL.TABLE_NAME, COLUMN_ID)
SELECT TR
FROM (
SELECT '0' AS TABLE_NAME,
'http://www.w3.org/TR/html4/loose.dtd">' AS TR,
0 AS ORD
FROM DUAL
UNION ALL
SELECT DISTINCT TABLE_NAME AS TABLE_NAME,
'<table style="width: 100%;"><tbody>' AS TR,
1 AS ORD
FROM TAB_LIST
UNION ALL
SELECT DISTINCT TABLE_NAME AS TABLE_NAME,
'<tr><th>테이블ID</th><td colspan="5">' || TABLE_NAME ||'</td></tr>'||
'<tr><th>테이블명</th><td colspan="5">' || T_COMMENTS ||'</td></tr>'||
'<tr><th>열이름</th><th>데이타유형</th><th>PK</th><th>NULLABLE</th><th>DEFAULT값</th><th>COMMENTS</th>' AS TR,
2 AS ORD
FROM TAB_LIST
UNION ALL
SELECT
TABLE_NAME AS TABLE_NAME,
'<tr><td>' || COLUMN_NAME ||
'</td><td>' || DATA_TYPE ||
'</td><td>' || C_KEY ||
'</td><td>' || NOTNULL ||
'</td><td>' || DATA_DEFAULT ||
'</td><td>' || C_COMMENTS ||'</td></tr>' AS TR,
RANK() OVER(PARTITION BY TABLE_NAME ORDER BY TABLE_NAME, COLUMN_ID) + 2 AS ORD
FROM TAB_LIST
UNION ALL
SELECT DISTINCT TABLE_NAME AS TABLE_NAME,
'</tbody></table><hr/>' AS TR,
998 AS ORD
FROM TAB_LIST
UNION ALL
SELECT 'ZZZZZZZZZZZZZZZZZZZZZZZZZZ' AS TABLE_NAME,
'</body></html>' AS TR,
999 AS ORD
FROM DUAL
)
ORDER BY TABLE_NAME, ORD
2) 실행 결과를 복사 한 후에 메모장에 붙여넣기 한다.(앞에 숫자까지 포함되지 않게 결과만 복사)
3) 메모장에 붙여 넣기 후 파일 확장자를 html로 저장한다(빨간색 box표시 참고)
4) 엑셀에서 해당 파일을 연다.(html 파일)
아래와 같은 양식으로 화면에 테이블 레이아웃 생성
