| Describe [message #371418] |
Tue, 17 October 2000 05:06  |
shad
Messages: 50
Registered: October 2000
|
Member |
|
|
I need to do a decribe of each table in a database. There are a lot of tables in a database so do not want to go to each table separetely. I want the name of the table to appear as well as the result of the describe.
e.g
sql> describe employee;
Name Null? Type
emp# Not Null Number(15)
Fname Not Null Varchar2(30)
etc
Because it is a lot of table I need to be able to see to which table the describe result belong.
Can anyone help?
|
|
|
|
| Re: Describe [message #371419 is a reply to message #371418] |
Tue, 17 October 2000 05:19  |
Sandeep Deshmukh
Messages: 13
Registered: October 2000
|
Junior Member |
|
|
You need to use trick to work out this.The familiar one is to spool the output of query to some file & then execute the file.Check whether following stuff work :
spool describe.sql
set heading off;
set feedback off;
select 'describe ' || tname || ';' from tab
where tabtype = 'TABLE';
spool off;
After this you can run command on sqlplus prompt as @describe.sql
Happy viewing..
|
|
|
|
| Re: Describe [message #371420 is a reply to message #371418] |
Tue, 17 October 2000 07:19 |
shad
Messages: 50
Registered: October 2000
|
Member |
|
|
|
This works but still does not include the table name. Whe running the describe file the has been spooled I still just get the results but I can not see to which tables the results belong because it is a lot of tables; thanks
|
|
|
|
| Re: Describe [message #371423 is a reply to message #371418] |
Tue, 17 October 2000 12:13 |
Prem
Messages: 79
Registered: August 1998
|
Member |
|
|
Shad,
Why not issue a select on the user_tab_columns datadictionary table. You can format the output the way you want
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE.... FROM USER_TAB_COLUMNS
and if u r executing this from sql*plus, you can also issue a BREAK ON TABLE_NAME just before executing the select so that the output will print the table name only once followed by the column names and data types
hth
Prem :)
|
|
|
|
| Re: Describe [message #371429 is a reply to message #371418] |
Tue, 17 October 2000 23:46 |
Sandeep Deshmukh
Messages: 13
Registered: October 2000
|
Junior Member |
|
|
Shad,
As you must be aware that prompt 'xxx' will display xxx on screen.You can modify the query to like :
select 'prompt Structure of ' || tname ||': ;'||'describe ' || tname || ';' from tab;
This will create a necessary script for you.But only trick you need to do in editor is to divide it into two staments.
Hint :The serch & replace can be handy.
Otherwise the solution given by Prem will be helpful.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
