By vitule


2008-10-15 17:54:49 8 Comments

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

21 comments

@parash 2019-07-09 13:35:41

Indeed, it is possible to have the list of tables via SQL queries.it is possible to do that also via tools that allow the generation of data dictionaries, such as ERWIN, Toad Data Modeler or ERBuilder. With these tools, in addition to table names, you will have fields, their types, objects like(triggers, sequences, domain, views...)

Below steps to follow to generate your tables definition:

  1. You have to reverse engineer your database
    • In Toad data modeler: Menu -> File -> reverse engineer -> reverse engineering wizard
    • In ERBuilder data modeler: Menu -> File -> reverse engineer

Your database will be displayed in the software as an Entity Relationship diagram.

  1. Generate your data dictionary that will contain your Tables definition
    • In Toad data modeler: Menu -> Model -> Generate report -> Run
    • In ERBuilder data modeler: Menu -> Tool -> generate model documentation

@technogeek1995 2019-07-09 14:14:29

It's important to include a working example when answering a question. Link only answers are not considered high quality answers.

@parash 2019-07-10 14:04:49

Thank you for your comment. I added an explanation.

@Kaushik Nayak 2019-03-31 15:49:04

A new feature available in SQLcl( which is a free command line interface for Oracle Database) is

Tables alias.

Here are few examples showing the usage and additional aspects of the feature. First, connect to a sql command line (sql.exe in windows) session. It is recommended to enter this sqlcl specific command before running any other commands or queries which display data.

SQL> set sqlformat ansiconsole     -- resizes the columns to the width of the 
                                   -- data to save space 

SQL> tables

TABLES
-----------
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
..

To know what the tables alias is referring to, you may simply use alias list <alias>

SQL> alias list tables
tables - tables <schema> - show tables from schema
--------------------------------------------------

 select table_name "TABLES" from user_tables

You don't have to define this alias as it comes by default under SQLcl. If you want to list tables from a specific schema, using a new user-defined alias and passing schema name as a bind argument with only a set of columns being displayed, you may do so using

SQL> alias tables_schema = select owner, table_name, last_analyzed from all_tables where owner = :ownr;

Thereafter you may simply pass schema name as an argument

SQL> tables_schema HR

OWNER   TABLE_NAME               LAST_ANALYZED
HR      DUMMY1                   18-10-18
HR      YOURTAB2                 16-11-18
HR      YOURTABLE                01-12-18
HR      ID_TABLE                 05-12-18
HR      REGIONS                  26-05-18
HR      LOCATIONS                26-05-18
HR      DEPARTMENTS              26-05-18
HR      JOBS                     26-05-18
HR      EMPLOYEES                12-10-18
..
..

A more sophisticated pre-defined alias is known as Tables2, which displays several other columns.

SQL> tables2

Tables
======
TABLE_NAME                 NUM_ROWS   BLOCKS   UNFORMATTED_SIZE COMPRESSION     INDEX_COUNT   CONSTRAINT_COUNT   PART_COUNT LAST_ANALYZED
AN_IP_TABLE                       0        0                  0 Disabled                  0                  0            0 > Month
PARTTABLE                         0        0                  0                           1                  0            1 > Month
TST2                              0        0                  0 Disabled                  0                  0            0 > Month
TST3                              0        0                  0 Disabled                  0                  0            0 > Month
MANAGE_EMPLYEE                    0        0                  0 Disabled                  0                  0            0 > Month
PRODUCT                           0        0                  0 Disabled                  0                  0            0 > Month
ALL_TAB_X78EHRYFK                 0        0                  0 Disabled                  0                  0            0 > Month
TBW                               0        0                  0 Disabled                  0                  0            0 > Month
DEPT                              0        0                  0 Disabled                  0                  0            0 > Month

To know what query it runs in the background, enter

alias list tables2

This will show you a slightly more complex query along with predefined column definitions commonly used in SQL*Plus.

Jeff Smith explains more about aliases here

@Kamil Ibadov 2017-02-16 07:01:57

There exists 3 datadictinary for this

DBA_TABLES describes all relational tables in the database.

SELECT owner, table_name
  FROM dba_tables

Description of relational tables accessible to the user

SELECT owner, table_name
  FROM all_tables

USER_TABLES describes the relational tables owned by the current user. This view does not display the OWNER column.

SELECT table_name
  FROM user_tables

@Rakesh Narang 2018-04-18 05:33:55

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;

@Brahmareddy K 2016-08-17 10:28:39

Oracle database to display the names of all tables using below query

SELECT owner, table_name FROM dba_tables;

SELECT owner, table_name FROM all_tables;

SELECT table_name FROM user_tables;

vist more : http://www.plsqlinformation.com/2016/08/get-list-of-all-tables-in-oracle.html

@Prashant Mishra 2016-06-21 06:18:34

Below is a commented snippet of SQL queries describing how options you can make use of:

-- need to have select catalog role
SELECT * FROM dba_tables;

-- to see tables of your schema
SELECT * FROM user_tables;

-- tables inside your schema and tables of other schema which you possess select grants on
SELECT * FROM all_tables;

@aim_thebest 2017-11-20 15:37:39

We can get all tables including column details from below query:

SELECT * FROM user_tab_columns;

@Punnerud 2017-10-25 11:44:29

Including views:

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

@Justin Cave 2008-10-15 17:56:47

SELECT owner, table_name
  FROM dba_tables

This is 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 Oracle tables that you probably don't care about.

Alternatively, 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 (ALL_TABLES shows you the information for all the tables that your user has been granted access to).

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

SELECT table_name
  FROM user_tables

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.

@vitule 2008-10-15 18:02:42

I'm getting an exception "ORA-00942: table or view does not exist"

@Justin Cave 2008-10-15 18:11:16

Then you haven't been given permission to see all the tables in the database. You can query the ALL_TABLES data dictionary view to see all the tables you are allowed to access, which may be a small subset of the tables in the database.

@Sam Saffron 2011-08-16 06:29:40

how does all_tables differ from user_tables? whats the deal with tabs/dict and cat? (I want to delete some answers and will be able to if you expand on those points)

@Justin Cave 2011-08-16 13:55:05

@Sam Saffron - Added some additional info on those two points.

@Gimhani 2018-08-27 06:19:09

Simple mistake if not an everyday sqlplus user: add the ending semi-colon(';') if u just don't get results with above commands :).

@saritonin 2019-02-25 17:40:39

Just a note that as of Oracle 12c, there is a column in the dba_users data dictionary to help remove system tables from your results set. The full query would be SELECT owner, table_name from dba_tables where owner not in (select username from dba_users where oracle_maintained = 'Y')

@Slava Babin 2016-10-09 16:09:54

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.

@Rusty 2016-11-11 15:31:02

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).

@Mateen 2014-11-09 12:36:16

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;

@Harshil 2014-10-08 09:28:32

    select object_name from user_objects where object_type='TABLE';

----------------OR------------------

    select * from tab;

----------------OR------------------

    select table_name from user_tables;

@Benyamin Limanto 2019-08-03 04:14:50

select * from tab is the simplest way, I forgot it. Thanks!

@Van Gogh 2014-03-07 18:11:21

With any of those, you can select:

SELECT DISTINCT OWNER, OBJECT_NAME 
    FROM DBA_OBJECTS 
    WHERE OBJECT_TYPE = 'TABLE' AND OWNER='SOME_SCHEMA_NAME';

SELECT DISTINCT OWNER, OBJECT_NAME 
    FROM ALL_OBJECTS 
    WHERE OBJECT_TYPE = 'TABLE' AND OWNER='SOME_SCHEMA_NAME';

@Jon Heller 2014-03-10 13:27:18

Yes but there are probably hundreds of other ways to get that information. If there's no advantage it shouldn't be listed here. This question is already full of too much information that will just confuse people looking for help.

@Van Gogh 2014-03-10 17:27:48

It's just like math language.

@mulquin 2015-04-22 04:31:12

I disagree @JonHeller, all options should be noted and the best one would get the most number of upvotes.

@Jon Heller 2015-04-22 05:38:39

@mulquin There are already 29(!) answers to this simple question. Luckily most of them are deleted and not everybody can see them. There's no need for anything other than the accepted answer. 99.9% of the time, if someone wants "a list of all tables", the accepted answer is the way to go. The rest is turning into code golf. Even these comments are just distracting users from important information.

@mulquin 2015-04-22 23:42:16

@JonHeller Agreed there is no need for it, but why limit the amount of methods that can accomplish the same task? The accepted answer is already the most prominent and like you said, 99.9% of people will use that solution. Is it really a bother if the 0.01% of users are distracted?

@cwd 2014-07-17 16:20:11

For better viewing with sqlplus

If you're using sqlplus you may want to first set up a few parameters for nicer viewing if your columns are getting mangled (these variables should not persist after you exit your sqlplus session ):

set colsep '|'
set linesize 167
set pagesize 30
set pagesize 1000

Show All Tables

You can then use something like this to see all table names:

SELECT table_name, owner, tablespace_name FROM all_tables;

Show Tables You Own

As @Justin Cave mentions, you can use this to show only tables that you own:

SELECT table_name FROM user_tables;

Don't Forget about Views

Keep in mind that some "tables" may actually be "views" so you can also try running something like:

SELECT view_name FROM all_views;

The Results

This should yield something that looks fairly acceptable like:

result

@Pablo Recalde 2016-01-27 08:40:12

thanks for the "better viewing" advice, but ain't you overwritting pagesize 30 with pagesize 1000?

@Israel Margulies 2012-12-21 16:32:06

Simple query to select the tables for the current user:

  SELECT table_name FROM user_tables;

@Mahmoud Ahmed El-Sayed 2012-04-25 17:30:17

Try the below data dictionary views.

tabs
dba_tables
all_tables
user_tables

@LPL 2012-04-25 17:43:50

What's wrong with the accepted answer?

@Mahmoud Ahmed El-Sayed 2012-04-26 22:09:47

accepted answers haven't any wrong but in this comment I list all data dictionary view I can use to get tables in database

@LPL 2012-04-26 22:20:14

But there isn't any new in your 'answer' and I think that's why you were downvoted instantly (not from me).

@praveen2609 2012-08-14 06:28:48

select * from dba_tables

gives all the tables of all the users only if the user with which you logged in is having the sysdba privileges.

@Mark J. Bobak 2013-11-24 05:49:04

That's actually not correct. SYSDBA is not required. You can get access to DBA_TABLES many ways. 1.) Direct grant on object to user by SYS. 2.) Grant of SELECT ANY DICTIONARY privilege to user. 3.) Grant of SELECT_CATALOG_ROLE role.

@stealth_angoid 2009-09-04 07:34:16

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.

@Gabe 2014-04-18 20:43:29

I did select * from cols and got 0 rows returned.

@vitule 2008-10-15 18:12:19

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

select table_name from all_tables  

@vitule 2008-10-15 18:16:30

For a more complete answer, see the accepted answer including the comments.

@Frank Schmitt 2013-04-26 10:39:25

@LimitedAtonement Sorry, that's plain wrong. The view is called user_tables, not user_table. If user_tables didn't work for vitule, something else was amiss.

@Eddie Awad 2008-10-15 18:08:50

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

Related Questions

Sponsored Content

46 Answered Questions

8 Answered Questions

[SOLVED] How does database indexing work?

37 Answered Questions

28 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2523472 View
  • 1707 Score
  • 28 Answer
  • Tags:   sql duplicates

17 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

27 Answered Questions

24 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

23 Answered Questions

[SOLVED] Insert into ... values ( SELECT ... FROM ... )

17 Answered Questions

41 Answered Questions

[SOLVED] Table Naming Dilemma: Singular vs. Plural Names

Sponsored Content