How to get the columns of all the DB tables using desc command? 3

[babe1898]

Hi all,

Greetings!

I'd like to know if it is possible to get columns of all the tables using the command desc?

I know the following command would give me the column information of the specified table.

desc TABLE_ONE;

But, what I want to do this for all the DB tables starting with TABLE. for instance, TABLE_ONE, TABLE_TWO, TABLE_THREE, etc...

I was thinking of doing something like

desc 'TABLE_%';


Is it possible to do such..Thank you in advance for your help.

Regards,
babe

 

[LKBrwnDBA]

Try:

set pages 0 term off trims on echo off 
spo desc_tab.sql
select 'desc '||table_name
  from user_tables where table_name like 'TABLE_%';
spo off
@@desc_tab

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[babe1898]

Hi LKBrwnDBA,

Thanks for the reply,

When I execute the code that you suggested, the output is shown below:



'DESC'||TABLE_NAME
-----------------------------------
desc TABLE_ONE
desc TABLE_TWO
.
.
.

Am I missing something? Thank you in advance..

Babe

 

[LKBrwnDBA]

Copy the following (including the SET statements) to a script file and execute:

set pages 0 term off trims on echo off 
spo desc_tab.sql
select 'desc '||table_name
  from user_tables where table_name like 'TABLE_%';
spo off
set term on echo on
@@desc_tab

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[Thargy]

Babe,

if you want to list all the columns of all the tables whose names begin with 'TABLE' then it's not essential to use desc.
If you run the following query it should do the trick.

SELECT TABLE_NAME,COLUMN_NAME
  FROM USER_TAB_COLUMNS
 WHERE SUBSTR(TABLE_NAME,1,5) = 'TABLE'
 ORDER BY TABLE_NAME, COLUMN_ID;

I am assuming that you are only interested in the tables in the current schema. If you really want to list every table, you must log on with DBA privileges, and run the same query, but select from DBA_TAB_COLUMNS instead, i.e.

SELECT TABLE_NAME,COLUMN_NAME
  FROM DBA_TAB_COLUMNS
 WHERE SUBSTR(TABLE_NAME,1,5) = 'TABLE'
 ORDER BY TABLE_NAME, COLUMN_ID;

Be advised that this will look through everything, so you may get more results than you bargained for. Oracle wisely does NOT name any of its tables using the word table, as this is a recipe for confusion.

If it's within your control, you should immediately rename all such tables to something meaningful ASAP. The word 'TABLE' is a reserved word in Oracle. As a general rule, you should never use reserved and/or keywords to name any database object.

Regards

T

 

[SantaMufasa]

Babe,

Did you run the above code from your SQL*Plus prompt?
LK's code is an excellent start. There are a couple of minor tweaks that will improve your results

set echo off
set pages 0 term off trims on echo off 
set linesize 50
set sqlprompt " "
set time off
spo desc_tab.sql
prompt spool describe_of_all_tables.txt
select 'desc '||table_name
  from user_tables where table_name like 'TABLE_%';
prompt spool off
prompt prompt Created file named "describe_of_all_tables.txt", which contains all table structures
spo off
set termout on
set echo on
@@desc_tab
set linesize 100
set sqlprompt "SQL> "

An important second "tweak" is to place the above code into a script, perhaps named, "Describe_all.sql". Then, from your SQL*Plus prompt, enter the command:

@Describe_all

...Qualified, if necessary with the path, if "Describe_all.sql" does not reside in your default path.

The session will look something like this:

SQL> @Describe_all
SQL> set echo off
 @@desc_tab
 spool describe_of_all_tables.txt
 desc TABLE1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 SEGMENTID                        NUMBER
 ROUTEID                          NUMBER
 LEFTFROM                         NUMBER
 LEFTTO                           NUMBER
 RIGHTFROM                        NUMBER
 RIGHTTO                          NUMBER

 desc TABLE1A
 Name                    Null?    Type
 ----------------------- -------- ----------------
 FIELD1                           NUMBER
 FIELD2                           NUMBER
 FIELD3                           NUMBER
 FIELD4                           NUMBER
 FIELD6                           NUMBER
...
Created file named "describe_of_all_tables.txt", which contains all table structures

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Sorry...I cross-posted with both LK and Tharg. But it also shows how many of us want to help.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[babe1898]

Hi all,

Thanks for all your replies...

Hi SantaMufasa,

I just want to let you know that I'm running the script from Oracle SQl Developer. I tried your suggestion to no avail. Am I missing something? Thanks a lot!


I tried the following as suggested by thargtheslayer, and it worked. Thans a lot...

SELECT TABLE_NAME,COLUMN_NAME
FROM USER_TAB_COLUMNS
WHERE SUBSTR(TABLE_NAME,1,5) = 'TABLE'
ORDER BY TABLE_NAME, COLUMN_ID;


Thanks in advance..

Babe

 

[SantaMufasa]

I'm running the script from Oracle SQL*Developer

...That explains much, since 15 of the 16 commands in the script I posted for you are SQL*Plus commands, and SQL*Developer does not recognise most SQL*Plus commands. <grin>

I presumed SQL*Plus for two reasons:[ul][li]First, you mentioned...

I'd like to know if it is possible to get columns of all the tables using the command desc

"DESC" is a SQL*Plus command, not native to SQL*Developer.

[/li][li]SQL*Plus is the lowest common denominator for all Oracle environments, our code posts here in all of the Oracle DBA Forums on Tek-Tips pre-suppose SQL*Plus unless the original poster designates an alternate environment.[/li][/ul]Sorry for any confusion.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]