SQL Show Tables

Introduction

In this chapter, we will learn how to list all the tables in a database using the SHOW TABLES statement. This command is useful for getting an overview of the tables available in your database. We will cover the syntax and provide examples to help you understand how to use this statement effectively.

Listing Tables in a Database

The SHOW TABLES statement is used to display a list of all tables in the currently selected database.

Syntax

SHOW TABLES;

Example

Let's assume we have a database named company and we want to list all the tables in this database.

Step-by-Step

1. Select the Database:

   First, ensure that you are using the correct database.

   USE company;
   

2. Show Tables:

   SHOW TABLES;
   

Example Output

If the company database contains three tables named employees, departments, and projects, the output will look like this:

+--------------------+
| Tables_in_company  |
+--------------------+
| employees          |
| departments        |
| projects           |
+--------------------+

Full Example

Let's go through a full example where we create a database, add some tables, and then use the SHOW TABLES statement to list them.

Step-by-Step

1. Create the Database:

   CREATE DATABASE company;
   

2. Select the Database:

   USE company;
   

3. Create Tables:

   CREATE TABLE employees (
       id INT PRIMARY KEY AUTO_INCREMENT,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       email VARCHAR(100)
   );
   
   CREATE TABLE departments (
       department_id INT PRIMARY KEY AUTO_INCREMENT,
       department_name VARCHAR(50) NOT NULL
   );
   
   CREATE TABLE projects (
       project_id INT PRIMARY KEY AUTO_INCREMENT,
       project_name VARCHAR(100) NOT NULL,
       start_date DATE,
       end_date DATE
   );
   

4. List All Tables:

   SHOW TABLES;
   

Full Example Script

-- Step 1: Create the Database
CREATE DATABASE company;

-- Step 2: Select the Database
USE company;

-- Step 3: Create Tables
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL
);

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

-- Step 4: List All Tables
SHOW TABLES;

Example Output

After executing the SHOW TABLES statement, you should see an output similar to this:

+--------------------+
| Tables_in_company  |
+--------------------+
| employees          |
| departments        |
| projects           |
+--------------------+

Conclusion

The SHOW TABLES statement is a simple yet powerful command that helps you manage and organize your database by listing all the tables. By understanding how to use this statement, you can quickly get an overview of the tables in your database.