Oracle SQL List Tables in Database or Schema

Do you need to list all tables in Oracle? There isn't an SQL list tables command, but you can learn how to show all tables in this article.

Oracle SQL List Tables

If you've forgotten the name of a specific table, or forgotten how to spell a table (was it plural or singular? One word or two with an underscore?), then you can use these queries to show all tables in Oracle SQL.

Oracle has several different built-in views that you can query to find the data you need. You can query any of these views to list all tables in Oracle.

You might not have the privileges to view each of these views, so if one query doesn't work, try another one.

List Tables Using DBA_TABLES

There isn't an Oracle show tables command specifically, but the first method is using the dba_tables view. This view shows all of the tables in the database.

1SELECT owner, table_name
2FROM dba_tables;

To be able to see this data, you need to have database administrator privileges. Otherwise, you'll get an error message saying the table or view does not exist.

List Tables Using ALL_TABLES

If you can't use the first method to show all tables, try querying the all_tables view to select all tables in SQL.

This view shows all of the tables in the database that are accessible to the user, whether or not they are the owner. It's the SQL list tables method that is available to most users.

1SELECT owner, table_name
2FROM all_tables;

To see this data, you don't need any special privileges (other than the ability to select from tables).

List Tables Using USER_TABLES

The final place you can check for a list of all tables in Oracle is the user_tables view. This views shows all of the tables that are owned by the current user or in the current schema.

1SELECT table_name
2FROM user_tables;

You don't need to specify the owner column because it's always the same value.

To see this data, you don't need any special privileges (other than the ability to select from tables).

List Tables and Views

If you're not sure if the object you're querying is a table or a view, you can also query the dba_views, all_views, or user_views objects.

These queries perform a UNION ALL to show you a list of all tables and views in the Oracle database.

DBA_TABLES and DBA_VIEWS

1SELECT 'Table' AS object_type, owner, table_name
2FROM dba_tables
3UNION ALL
4SELECT 'View', owner, view_name
5FROM dba_views;

ALL_TABLES and ALL_VIEWS

1SELECT 'Table' AS object_type, owner, table_name
2FROM all_tables
3UNION ALL
4SELECT 'View', owner, view_name
5FROM all_views;

USER_TABLES and USER_VIEWS

1SELECT 'Table' AS object_type, table_name
2FROM user_tables
3UNION ALL
4SELECT 'View', view_name
5FROM user_views;

So, that's how you can list all tables in Oracle SQL. There isn't an "SQL list tables" command but you can query one of these three views to show tables in Oracle.

Lastly, if you enjoy the information and career advice I've been providing, sign up to my newsletter below to stay up-to-date on my articles. You'll also receive a fantastic bonus. Thanks!