Get list of all tables in Oracle?

Question

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

Answer 1

The following query only list the required data, whereas the other answers gave me the extra data which only confused me.

select table_name from user_tables;

Answer 2

You can use Oracle Data Dictionary to get information about oracle objects.

You can get list of tables in different ways:

select * 
from dba_tables

or for example:

select * 
from dba_objects 
where object_type = 'TABLE' 

Then you can get table columns using table name:

select * 
from dba_tab_columns

Then you can get list of dependencies (triggers, views and etc.):

select * 
from dba_dependencies
where referenced_type='TABLE' and referenced_name=:t_name 

Then you can get text source of this objects:

select * from dba_source

And you can use USER or ALL views instead of DBA if you want.

Answer 3

Including views:

SELECT owner, table_name as table_view
  FROM dba_tables
UNION ALL
SELECT owner, view_name as table_view
  FROM DBA_VIEWS

Answer 4

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 5

I did not find answer which would point to use

DBA_ALL_TABLES (ALL_ALL_TABLES/USER_ALL_TABLES)

so decided to add my version as well. This view actually returns more that DBA_TABLES as it returns object tables as well (http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_1003.htm).

Answer 6

I was looking to get a list of all columns names belonging to a table of a schema sorted by the order of column id.

Here's the query I am using: -

SELECT COLUMN_NAME
FROM ALL_TAB_COLUMNS
WHERE OWNER = 'schema_owner_username' AND TABLE_NAME='table_name'
ORDER BY COLUMN_ID ASC;