MySQL SHOW Tables

Introduction

In this chapter, we will learn how to list all the tables in a MySQL database. This is useful for quickly seeing what tables are available and verifying that tables have been created correctly. We will cover the syntax and examples for using the SHOW TABLES statement.

Showing All Tables

To display all the tables in the current database, we use the SHOW TABLES statement. This command lists all tables in the selected database.

Syntax

SHOW TABLES;

Example

SHOW TABLES;

Output

Tables_in_database_name
employees
departments
projects

This output shows the names of all tables in the current database.

Full Example

Let's go through a full example where we create a database, create a few tables, and then list all tables in the database.

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Tables:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    start_date DATE,
    end_date DATE
);

4. Show All Tables:

SHOW TABLES;

Output

Tables_in_company
employees
departments
projects

Important Considerations

• Database Selection: Ensure you have selected the correct database using the USE statement before running SHOW TABLES.
• Permissions: You need the appropriate permissions to view tables in the database. Typically, this requires at least read access to the database.

Conclusion

The SHOW TABLES statement is a simple but powerful command for listing all tables in a MySQL database. This chapter covered how to use SHOW TABLES to see what tables exist in your database, providing a quick way to verify table creation and manage your database structure.