Home » RDBMS Server » Server Administration » Help with sql script
Help with sql script [message #373716] Wed, 02 May 2001 13:06 Go to next message
Arpit
Messages: 99
Registered: March 2001
Member
Hi,

I'm trying to generate a list of all the tables using 'desc
. What I need to have is Print the table name in the generated list.

Ex:
====================
Table Name : Contact
====================
Contact_No NOT NULL NUMBER(6)
Name NOT NULL VARCHAR2(30)

If I generate a log file using the following in the script

DESC CONTACT;
DESC CLIENT;
DESC EMPLOYEE;
it doesn't show the table name.

Please help.
Re: Help with sql script [message #373717 is a reply to message #373716] Wed, 02 May 2001 13:33 Go to previous message
Andrew again...
Messages: 270
Registered: July 2000
Senior Member
change this to your needs - note my excluded tables.

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

@tmp_script.sql
Previous Topic: Re: sql*plus spooling error
Next Topic: Host String / Database/Password.
Goto Forum:
  


Current Time: Mon Dec 23 09:27:16 CST 2024