In this tutorial, you will learn how to connect to your MySQL server, select a database, and list all tables within that database. We will explore various commands such as SHOW TABLES, SHOW FULL TABLES, and queries on INFORMATION_SCHEMA. Additionally, we’ll look at how to describe a specific table and list tables using MySQL Workbench.

Prerequisites

Before we start, ensure you have the following:

  • MySQL installed on your system.
  • Access to a MySQL database (you should have login credentials).
  • Basic understanding of MySQL commands.

Step 1- Connecting to MySQL

To begin, you need to connect to your MySQL server. Open your terminal or command prompt and use the following command:

mysql -u root -p

After entering your password, you’ll be connected to the MySQL shell. If you see the mysql> prompt, you’re good to go.

Step 2 – Selecting the Database

Once connected, select the database you want to work with. Use the USE command followed by the database name:

USE your_database_name;

For example, if your database is named test_db, the command would be:

USE test_db;

After executing this command, you should see the message “Database changed.”

Step 3 – Listing Tables with SHOW TABLES

The simplest way to list all tables in your selected database is by using the SHOW TABLES command. Execute the following:

SHOW TABLES;

This will display all the tables in the current database. Here’s what the output might look like:

+-------------------+
| Tables_in_test_db |
+-------------------+
| departments       |
| employees         |
| salaries          |
+-------------------+
3 rows in set (0.01 sec)

As you can see, the SHOW TABLES command is straightforward and provides a quick overview of all tables in the database.

Step 4 – Listing Tables with SHOW FULL TABLES

To get more detailed information about the tables, including whether they are base tables or views, use the SHOW FULL TABLES command:

SHOW FULL TABLES;

This command will show the table type along with the table names:

+-------------------+------------+
| Tables_in_test_db | Table_type |
+-------------------+------------+
| departments       | BASE TABLE |
| employees         | BASE TABLE |
| salaries          | BASE TABLE |
+-------------------+------------+
3 rows in set (0.00 sec)

Step 4 – Describing a Specific Table

If you want more details about a specific table, you can use the DESCRIBE command. For instance, to describe the employees table, use:

DESCRIBE employees;

This command will output the structure of the employees table, including column names, data types, and other attributes:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no     | int           | NO   | PRI | NULL    |       |
| birth_date | date          | NO   |     | NULL    |       |
| first_name | varchar(14)   | NO   |     | NULL    |       |
| last_name  | varchar(16)   | NO   |     | NULL    |       |
| gender     | enum('M','F') | NO   |     | NULL    |       |
| hire_date  | date          | NO   |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

Step 5 – Using INFORMATION_SCHEMA to List Tables

Another way to list tables is by querying the INFORMATION_SCHEMA database, which stores metadata about all your databases. Here’s how to retrieve table names from INFORMATION_SCHEMA:

SELECT table_name 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_schema = 'test_db';

Replace your_database_name with the name of your database. This query provides the same result as SHOW TABLES but can be more flexible for complex queries.

Here’s an example output:

+-------------+
| TABLE_NAME  |
+-------------+
| departments |
| employees   |
| salaries    |
+-------------+
3 rows in set (0.01 sec)

Step 6 – Show MySQL Tables from the Command Line

For users who prefer command-line interfaces, you can also list MySQL tables directly from the command line without entering the MySQL shell.

For example, if your username is root and your database is test_db, the command would be:

mysql -u root -p -e 'SHOW TABLES;' test_db

After entering your password, the tables in the specified database will be listed directly in your terminal.

+-------------------+
| Tables_in_test_db |
+-------------------+
| departments       |
| employees         |
| salaries          |
+-------------------+

Conclusion

In this article, we explored various methods to list tables in a MySQL database. We covered the SHOW TABLES command for a quick overview, the SHOW FULL TABLES command for detailed table types, and the DESCRIBE command for table details. Additionally, we learned how to use INFORMATION_SCHEMA for more advanced queries. You can try to use MySQL on dedicated server hosting from Atlantic.Net!