--------------------------------------------------------
-- formatTableContents.sql
-- VERSION 1.0
-- written by ForeverLee
-- This SQL Plus script lists Contents by table.
--------------------------------------------------------

SET VER OFF
---------GET TABLE COMMENTS-----------
column t_name new_value tb_name;
column t_comments new_value tb_comments;
select TABLE_NAME t_name,COMMENTS t_comments
  from user_tab_comments
 where TABLE_NAME='&table_name';

SET ECHO OFF
SET FEEDBACK OFF
SET TERM ON
set pagesize 50
set linesize 160
col COLUMN_ID for a10
col DATA_TYPE for a10
col COLUMN_NAME for a20
col COMMENTS for a80
col DATA_LENGTH for a10
col DATA_DEFAULT for a10
col NULLABLE for a10

---------MARKUP HTML----------------
SET MARKUP HTML ON SPOOL ON

SPOOL c:&&tb_name..html

prompt 表名: &&tb_name -- &&tb_comments
select dtc.COLUMN_ID 字段编号,      
       dtc.COLUMN_NAME 字段名称,
       dtc.DATA_TYPE 字段类型,
       DECODE(dtc.DATA_TYPE,'NUMBER',dtc.DATA_LENGTH/2,dtc.DATA_LENGTH) 字段长度,
       dtc.DATA_DEFAULT 默认值,
       dtc.NULLABLE 可空,
       dcc.COMMENTS 注释
 from  USER_COL_COMMENTS dcc,USER_TAB_COLUMNS dtc
where  dcc.table_name='&tb_name'
  and  dcc.table_name=dtc.table_name
  and  dcc.column_name=dtc.column_name
order by dtc.COLUMN_ID;
SPOOL OFF
---------END MARKUP HTML----------------

SET MARKUP HTML OFF
SET ECHO ON
SET FEEDBACK ON
SET VER ON

[@more@]