List of "view" tables and List of Fields in a table

[Kenhardwick]

The following SQL gives me a list of all tables in<br>
our Oracle database. FYI - I am executing this SQL using<br>
VBA in Excel using Oracle73 driver.<br>
<br>
Select TABLE_NAME<br>
from ALL_TABLES<br>
<br>
I would also like to get a list of &quot;view&quot; tables. <br>
How would I do that ?<br>
<br>
Also, I would like to list all fields and their attributes<br>
from a specific table. <br>
How would I do that ?<br>
<br>
<br>
TIA,<br>
<br>
Ken Hardwick<br>
<br>
<br>

 

[connectxyzzy]

If you do a query against 'dictionary' and look for table_name like 'ALL%', you should get a list of the accessable tables that you can query for you to construct other queries. In particular, you can describe (in SQL*Plus) all_ind_columns, all_views (make sure the column 'TEXT' is long enough), all_tab_columns, etc.<br>
<br>
BTW, dictionary has two columns, table_name and comments, where comments tells you what that table is about.<br>
<br>
Hope this helps

 

[carp]

Ken -<br>
<br>
First, ALL_TABLES will not necessarily give you all of the tables in the database. It lists the tables that you have access to. If you are using one of the privileged roles like DBA, SYSOPER, etc, then you will see all of the tables. Otherwise, you will only see the tables in your schema and any tables in other schemas on which you have been granted privileges. If you are interested in all of a given class of objects in a database (tables, views, triggers, etc), then you should be querying views such as DBA_TABLES, DBA_VIEWS, etc.<br>
<br>
DBA_VIEWS will list not only all of the views (which, by the way, are not tables), but also the queries that define them.<br>
<br>
To find the columns and attributes of a table, you could try<br>
<br>
SELECT *<br>
FROM dba_tab_columns<br>
WHERE table_name = 'THE_TABLE_YOU_ARE_INTERESTED_IN';<br>
<br>
However, you will probably want to narrow down the column attributes you are interested in - replace * with the attributes you wish to see (e.g., data_type, data_length, etc).

 

[samw1]

The following sql will give you all table_names and view names:<br>
<br>
select Owner, object_name, object_type<br>
from all_objects<br>
where object_type in ( 'TABLE','VIEW')<br>
;<br>
<br>
<br>
<br>
To list all fields and their attributes<br>
from a specific table or view simple use<br>
desc owner.table_name or<br>
desc owner.view_name<br>
<br>
Hope this will help.<br>
<br>
<br>
<br>
<br>