/*
|| tab_descr.sql
|| Run this script in the current user to generate a report describing the
|| the tables. 7.x and above compatible.
|| AHM, 01/30/2001, Original version
*/
set pagesize 0
set feedback off
set verify off
column a new_val todaysdate
select to_char(sysdate,'DD-MON-YYYY HH24:MI') a from dual;
column b new_val thisuser
column c new_val thisdb
select to_char(sysdate,'DD-MON-YYYY HH24:MI') a,
user b,
substr(global_name, 1, 20) c
from global_name;
column Table_Descr format a80 wrap

spool tmp_script.sql
--PROMPT spool &thisuser._&thisdb..txt
PROMPT prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT prompt TABLE DEFINITION REPORT &todaysdate
PROMPT prompt FOR SCHEMA OWNER &thisuser @ &thisdb
PROMPT prompt NB: Only Tables are described - NOT Synonyms.
PROMPT prompt . Tables excluded: MLOG$%, %_H
PROMPT prompt Created by tab_descr.sql
PROMPT prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PROMPT prompt
PROMPT prompt

SELECT 'prompt Table #'||to_char(ROWNUM, '000')||' - '||owner||'.'||table_name||chr(10),
' descr '|| owner||'.'||table_name||';' Table_Descr
FROM (SELECT t1.table_name, t1.owner
FROM all_tables t1
WHERE t1.table_name NOT LIKE 'MLOG$%'
AND t1.table_name NOT LIKE '%_H'
AND t1.owner = upper('&thisuser')
GROUP BY t1.table_name, t1.owner);
PROMPT prompt ~~~ END OF REPORT ~~~
PROMPT spool off
PROMPT prompt your output is in : &thisuser._&thisdb..txt
SPOOL off

SET MARKUP HTML ON SPOOL ON HEAD "<TITLE>SQL*Plus Report</title> -
<STYLE TYPE='TEXT/CSS'><!--BODY {background: ffffc6} --></STYLE>"
SET ECHO OFF
spool &thisuser._&thisdb..html
@tmp_script.sql
SPOOL off
SET MARKUP HTML OFF