Oracle: get list of all tables?

Question

How do I query an Oracle database to display the names of all tables in it?

Answer 1

SELECT owner, table_name
  FROM dba_tables

assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table or that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role either of which would allow you to query any data dictionary table. Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of tables that you probably don't care about because those are all delivered by Oracle.

If you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLES view

SELECT owner, table_name
  FROM all_tables

although that may be a subset of the tables available in the database

Answer 2

Try selecting from user_tables which lists the tables owned by the current user.

Answer 3

Querying user_tables and dba_tables didn't work.
This one did:

select table_name from all_tables

Answer 4

select table_name from tabs;

Answer 5

SELECT * FROM USER_TABLESPACES

Answer 6

select * from tabs;

shorter version...

Answer 7

Going one step further, there is another view called cols (all_tab_columns) which can be used to ascertain which tables contain a given column name.

For example:

SELECT table_name, column_name
FROM cols
WHERE table_name LIKE 'EST%'
AND column_name LIKE '%CALLREF%';

to find all tables having a name beginning with EST and columns containing CALLREF anywhere in their names.

This can help when working out what columns you want to join on, for example, depending on your table and column naming conventions.

Answer 8

select * from tab;

also shorter

Answer 9

Thanks :) select table_name froma all_tables is great :)

Answer 10

Once I've gotten the names of the tables, is there an easy way to loop through them and do a 'describe' on each table having the output show up either on screen or in a spool file?

Answer 11

the select table_name from all_tables does not work in sql 2005

Answer 12

select * from cat;

Answer 13

select * from dict;