Describing all the tables in a database [message #374495] |
Thu, 14 June 2001 17:42 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Asim
Messages: 8 Registered: October 2000
|
Junior Member |
|
|
Hi
I am trying to describe all the tables in a database.
We use desc or describe tablename; to describe a table, but what is the command to describe all the tables in a database (i don't need the system tables)
Once i log into sqlplus as a say ABC (SID or HostString) as a user then if i do a desc table name i get column name, data type and null not null etc but i i need that for all the tables in that ABC database
Thanks
-Asim
|
|
|
Re: Describing all the tables in a database [message #374524 is a reply to message #374495] |
Fri, 15 June 2001 18:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Andrew again...
Messages: 270 Registered: July 2000
|
Senior Member |
|
|
try this in sqlplus...
/*
|| 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
@tmp_script.sql
|
|
|
|
|
|
Re: Describing all the tables in a database [message #478144 is a reply to message #478142] |
Wed, 06 October 2010 09:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I don't believe it is tweakable in the way you want.
It just runs a set of describe commands and spools them to a file.
To work in excel you'd need to put seperators between the columns of the describe output and I can't see anyway to do that. It appears desc is always space delimeted - which isn't much use.
To get what you want you'd need to query the data dictiony views - user_tab_cols would be the most usful. If you write an actual query then you can delimit it any way you want.
|
|
|
|
Re: Describing all the tables in a database [message #478158 is a reply to message #478142] |
Wed, 06 October 2010 12:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/61988.jpg) |
LKBrwn_DBA
Messages: 487 Registered: July 2003 Location: WPB, FL
|
Senior Member |
|
|
hem5840 wrote on Wed, 06 October 2010 10:44Blah, blah,blah...and gives me mostly what I want, but I would like it tweaked a bit as I said. Can anyone help? Many thanks.
Try this:
/*
|| 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
[Updated on: Wed, 06 October 2010 12:12] by Moderator Report message to a moderator
|
|
|
|