Home » Tutorials » SQL » How to List All Tables in SQL

How to List All Tables in SQL

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%';



Link/cite this page

If you use any of the content on this page in your own work, please use the code below to cite this page as the source of the content.

  • Stewart, Suzy. "How to List All Tables in SQL". After Hours Programming. Accessed on May 21, 2022. https://www.afterhoursprogramming.com/tutorial/sql/list-all-tables/.

  • Stewart, Suzy. "How to List All Tables in SQL". After Hours Programming, https://www.afterhoursprogramming.com/tutorial/sql/list-all-tables/. Accessed 21 May, 2022.

  • Stewart, Suzy. How to List All Tables in SQL. After Hours Programming. Retrieved from https://www.afterhoursprogramming.com/tutorial/sql/list-all-tables/.



Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.