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.
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.
If you’re using a command line, you can use the dt command to display all tables:
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:
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.
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.
1 thought on “SQL Show Tables: List All Tables in a Database”
In Oracle, there is one other way to see all tables.
select * from tab;