MySQL List All Tables

Introduction

MySQL is one of the most well-known open-source and cost-free database management software systems (DBMS). It is famous for its quickness and for having an intuitive UI. When working as a database administrator for a large company, you often have to go through many databases and their tables. We will, therefore, discover how to list or display tables in the MySQL shell in this post.

One of the most crucial things to do as a database administrator overseeing a MySQL server is to familiarize yourself with the MYSQL environment.

It is crucial to display or list the MySQL table if your server has several databases, each with multiple tables. You may get details about user accounts and their permissions with this query.

You may list tables in a MySQL database with the MySQL SHOW table's command, which is called.

By the time this post ends, you will know:

• The meaning of the directive
• How to make use of it
• Why it's beneficial initially and
• An alternate method to the show tables command for listing the tables in a database

Prerequisites

If you wish to follow along with my examples, there are just two requirements. I first assume a basic understanding of MySQL and SQL. Furthermore, a local installation of MySQL ought to be available to you.

Since CoderPad MySQL Sandbox will be your MySQL client for this article, It's a great approach to begin writing SQL quickly and simply.

Why and how do MySQL SHOW tables work?

To list the tables in a database in MySQL, use the show tables command to specify the power that is essentially it. However, what applications does it have? After all, you may simply show the tables inside a database, usually in a tree, using a client with the MySQL GUI (graphical user interface).

First, not every MySQL interaction will allow you to use a graphical user interface. Show tables are helpful if all you have is the command line. Remember that this command is quite versatile as well. You may use WHERE and LIKE clauses to filter the result further while utilizing them.

Lastly, you may use this in scripts since it is a command. Thus, there's a chance to automate specific processes that a graphical user interface (GUI) couldn't handle.

In MySQL, how can I see tables?

CREATE TABLE students (


 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,


    name VARCHAR(250),


    email VARCHAR(100));


CREATE TABLE teachers (


    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,


    name VARCHAR(250),


    email VARCHAR(100));

In the sandbox, display tables. The following tables need to be displayed:

SHOW [EXTENDED] [FULL] TABLES


    [{FROM | IN} db_name]


    [LIKE 'pattern' | WHERE expr]

In a MySQL database, you take the following actions to list tables:

• First, log in using a MySQL client, such as MySQL, To access the MySQL database server.
• Second, use the USE command to change to a particular database.
• Third, execute the command SHOW TABLES.

The MySQL SHOW TABLES command's syntax is demonstrated by the following:

SHOW TABLES;

You can see how to list the table in the classic model's database by looking at the following example.

• Step 1: Establish a connection to the MySQL server:

>mysql -u root -p


Enter password: **********


Mysql>

• Step 2: Use the classic model's database instead.

mysql> use classic models;


Database changed


mysql>

• Step 3: Display tables from the database for traditional models:

> show tables;

You may see whether a table is a base table or a view using the SHOW TABLES command. You use the following version of the SHOW TABLES command to include the table type in the result.

SHOW FULL TABLES


WHERE Table_type = 'BASE TABLE';

For the sake of illustration, let's construct a view in the classic model's database called contacts that contain the first name, last name, and phone from the customer and employee tables:

CREATE VIEW contacts


AS


SELECT lastName, firstName, extension as phone


FROM employees


UNION


SELECT contact first name, contact last name, phone


FROM customers;

You now provide the command SHOW FULL TABLES:

> SHOW FULL TABLES

As you can see, except for the contacts table, which is a view, every table is the base table.

Displaying every table at once may not be natural for a database with many tables.

Thankfully, the SHOW TABLES command gives you the ability to filter the returned tables as follows by using the LIKE operator or an expression in the WHERE clause:

Additionally, SHOW TABLES allows a LIKE clause, which may be used to limit the results to just those tables that fit a certain pattern:

SHOW TABLES


LIKE 'a%';

Summary

Following this instruction, we learned to show or list tables in the MySQL database. We now know how to use pattern matching to filter the output.