In this SQL tutorial, you will learn which commands to use to list all tables of a database in different database management systems.
Each database system has its own command to show all tables in a specified database. Here you can find the respective SQL command to list all tables in MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.
SQL command to list all tables in MySQL
To list all tables in MySQL, first, you connect to the MySQL database server using the following command:
mysql -u username -p
MySQL then prompts for the password; just enter the correct one for the user and press enter.
After that, select a database to work with:
use database_name;
And finally, issue the SHOW TABLES command to display all tables in the current database:
show tables;
SQL command to list all tables in Oracle
In Oracle, you can use the SQL*Plus or SQL Developer connect to the Oracle Database server and show all tables in a database. Then issue one of the following SQL statement:
1) Show all tables owned by the current user:
SELECT
table_name
FROM
user_tables;
2) Show all tables in the current database:
SELECT
table_name
FROM
dba_tables;
3) Show all tables that are accessible by the current user:
SELECT
table_name
FROM
all_tables;
SQL command to list all tables in PostgreSQL
For PostgreSQL, you can use the psql command-line program to connect to the PostgreSQL database server and display all tables in a database.
First, connect to the PostgreSQL Database server:
psql -d database_name -U user -W
PostgreSQL will prompt for the password; just enter the correct one and press enter.
Then, issue the following command to show all tables in the current database:
\dt
If you want to display also the size and description of the tables, you the following command:
\dt+
SQL command to list all tables in SQL Server
In SQL Server, you can use the following query to find all tables in the currently connected database:
SELECT
*
FROM
information_schema.tables;
SQL command to list all tables in DB2
First, connect to a specific database on the DB2 database server:
db2 connect to database_name
Second, to list all table in the current database schema, you use the following command:
db2 list tables for schema schema_name
To list all tables, you use the command below:
db2 list tables for all
SQL command to list all tables in SQLite
To show all tables in the current SQLite database, you use the following command:
.tables
If you want to query the tables based on a specific pattern e.g., all tables whose names start with test, you use the following command:
.tables 'test%';