get table names, then column names

[mcambray]

Brand new to Oracle9, need to know equivalent of Informix queries "info columns for tablename;" and "select tabname from systables;" Any help is appreciated.

 

[oradba101]

Hi, mcambray

You can get both from:

SELECT table_name, column_name
FROM dba_tab_columns

Regards,



William Chadbourne
Oracle DBA

 

[SantaMufasa]

If you query the DBA_TAB_COLUMNS, I suggest a couple of slight adjustments to the query:

col owner format a30
col table_name format a30
break on owner on table_name
set linesize 200
SELECT owner, table_name, column_name
  FROM dba_tab_columns
 ORDER BY owner,table_name, column_id;

The adjustments:

1) Accommodate multiple Oracle users owning tables with the same name
2) Ensure that the columns appear in "natural" (physical) order
3) Ensure that everything appears by owner, by table, by column order.



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

 

[mcambray]

Thanks for help, but I get "table or view does not exist"
message.

 

[oradba101]

Hi, mcambray

This is probably because you don't have DBA privileges. Try this instead:

SELECT table_name, column_name
FROM user_tab_columns

Regards,


William Chadbourne
Oracle DBA

 

[mcambray]

Again thanks, no error this time, but also no results.

 

[SantaMufasa]

McAmbray,

Change the reference, "user_tab_columns", to "all_tab_columns"...Then you will see results. (The reason that you saw no results with "user_tab_columns" is because the Oracle user with which you connected to the database owns now tables/views itself.

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

 

[mcambray]

Super! Thanks again. So I got a listing with 1 line containing the table name and column name for each column.