| Help with sql script [message #373716] |
Wed, 02 May 2001 13:06  |
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  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
