SQL Show Tables: List All Tables in a Database

Do you need to get a list of all the tables in your SQL database?

Learn how to do this in several different database vendors in this guide.

 

SQL Show Tables

Sometimes you need to get a list of tables from your database. This could be to help with testing, to see what tables exist before you create a table or remove one, or some other reason.

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 your database.

Each database vendor has a different way of showing tables. Sometimes there is a command, others have a SELECT query from the data dictionary.

Let’s take a look at a few ways in each database.

 

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

 

User Owned Tables

To see tables owned by the currently logged-in user, you can query the user_tables view.

SELECT table_name
FROM user_tables
ORDER BY table_name ASC;

This only shows tables owned by the current user. It doesn’t include tables owned by other users that the current user can see.

 

User Accessible Tables

To see all tables that the current user can access, you can query the all_tables view.

SELECT table_name
FROM all_tables
ORDER BY table_name ASC;

You can add the owner column to your view to see who owns the table:

SELECT table_name, owner
FROM all_tables
ORDER BY table_name ASC;

This may show you a lot of results, including a lot of system tables. You can add a WHERE clause for the owner field to filter on the owner.

 

All Database Tables

If you want to list all tables in the Oracle database, you can query the dba_tables view.

SELECT table_name
FROM dba_tables
ORDER BY table_name ASC;

This view (and all others starting with dba_) are meant for database administrators. If you don’t have admin rights, you’ll get this error:

ORA-00942: table or view does not exist.

In order to see this view, you’ll need either of these privileges:

• the dba_tables view
• the SELECT ANY DICTIONARY privilege
• the SELECT_CATALOG_ROLE role

 

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

SELECT 'Table' AS object_type, owner, table_name
FROM dba_tables
UNION ALL
SELECT 'View', owner, view_name
FROM dba_views;

 

ALL_TABLES and ALL_VIEWS

SELECT 'Table' AS object_type, owner, table_name
FROM all_tables
UNION ALL
SELECT 'View', owner, view_name
FROM all_views;

 

USER_TABLES and USER_VIEWS

SELECT 'Table' AS object_type, table_name
FROM user_tables
UNION ALL
SELECT 'View', view_name
FROM user_views;

 

Show Tables in SQL Server

There are a few ways to list tables in SQL Server.

 

All Tables and Views

The easiest way to find all tables in SQL is to query the INFORMATION_SCHEMA views.

You do this by specifying the information schema, then the “tables” view.

Here’s an example.

SELECT table_name, table_schema, table_type
FROM information_schema.tables
ORDER BY table_name ASC;

This will show the name of the table, which schema it belongs to, and the type.

The type will either be “BASE TABLE” for tables or “VIEW” for views.

 

All Tables Only

To see a list of only tables and not views, you can filter on the table_type column.

SELECT table_name, table_schema
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_name ASC;

 

SQL Server 2000

If you’re running SQL Server 2005 you can use the information_schema method above. If you’re on 2000 or earlier, then you’ll need to use a different method.

You can query the SYSOBJECTS view to find all of the tables in the database. This shows all objects, so to filter it to tables we can filter on the xtype column equals the value of “U”, which represents a user table.

Here’s the query:

SELECT *
FROM sysobjects
WHERE xtype = 'U'
ORDER BY name ASC;

 

Show Tables in MySQL

There are a few ways to list tables in MySQL.

 

Show Tables Command

You can run the command SHOW TABLES once you have logged on to a database to see all tables.

SHOW TABLES;

The output will show a list of table names, and that’s all.

 

Show Table Type

You can use the optional FULL modifier which shows the table type as well.

SHOW FULL TABLES;

This will show the table name and the table type, which is either VIEW or BASE TABLE.

 

Show Tables in Another Database

You can use this command to see a list of tables in another database as well.

SHOW TABLES FROM database_name;

 

Show Tables Matching a Pattern

When selecting from a view in other databases, you can use LIKE to filter the tables that match a certain string.

You can do the same with the SHOW TABLES command:

SHOW TABLES LIKE string;

To see all tables that include the letter “user”, you can run this command.

SHOW TABLES LIKE '%user%';

 

Show Tables in PostgreSQL

There are a couple of ways to view a list of tables in PostgreSQL.

 

Show Tables

If you’re using a command line, you can use the dt command to display all tables:

\dt

This won’t work in an IDE, but there is another method for that.

 

Show Tables and Descriptions

If you’re using a command line, you can use the dt command to display all tables plus the table descriptions:

\dt+

As above, there is another method if you’re not using the command line.

 

Select from Catalog

If you want another method, or if you’re using an IDE and can’t use \dt, then you can select from the pg_catalog schema.

Here’s an example:

SELECT tablename, schemaname, tableowner
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog'
AND schemaname != 'information_schema'
ORDER BY tablename ASC;

This will show you information for all tables in the database. The WHERE clause filters out system tables, but you can omit the WHERE clause and see those tables if you need to.

 

Conclusion

There are several ways for each vendor to see a list of tables in the database. Some are inbuilt commands and others are selecting from database views. Showing a list of databases is done in a similar way.

If you want to learn and remember the main features of each SQL database, grab the SQL Cheat Sheets below.