Here are five ways to check whether or not a table exists in a MySQL database.

The table_exists() Procedure

In MySQL, the sys.table_exists() stored procedure tests whether a given table exists as a regular table, a TEMPORARY table, or a view. The procedure returns the table type in an OUT parameter.

Example:

CALL sys.table_exists('Music', 'Albums', @table_type); 
SELECT @table_type;

Result:

+-------------+
| @table_type |
+-------------+
| BASE TABLE  |
+-------------+

Note that if both a temporary and a permanent table exist with the given name, TEMPORARY is returned.

The information_schema.TABLES Table

Another way to check whether a table exists is to query the information_schema.TABLES table:

SELECT 
   TABLE_SCHEMA, 
   TABLE_NAME,
   TABLE_TYPE
FROM 
   information_schema.TABLES 
WHERE 
   TABLE_SCHEMA LIKE 'music'    AND 
	TABLE_TYPE LIKE 'BASE TABLE' AND
	TABLE_NAME = 'Artists';

Result:

+--------------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE |
+--------------+------------+------------+
| Music        | Artists    | BASE TABLE |
+--------------+------------+------------+

In this case, I returned the base table called Artists from the database called music. Querying this without filtering the results by TABLE_SCHEMA returns base tables from all databases. Querying it without filtering by TABLE_TYPE returns all table types.

If we don’t need all that info, we can do this:

SELECT EXISTS (
    SELECT 
        TABLE_NAME
    FROM 
    information_schema.TABLES 
    WHERE 
    TABLE_SCHEMA LIKE 'music' AND 
        TABLE_TYPE LIKE 'BASE TABLE' AND
        TABLE_NAME = 'Artists'
    );

Result:

1

Or we could get the count:

SELECT COUNT(TABLE_NAME)
FROM 
   information_schema.TABLES 
WHERE 
   TABLE_SCHEMA LIKE 'music' AND 
	TABLE_TYPE LIKE 'BASE TABLE' AND
	TABLE_NAME = 'Artists';

Result:

+-------------------+
| COUNT(TABLE_NAME) |
+-------------------+
|                 1 |
+-------------------+

The SHOW TABLES Command

The SHOW TABLES command lists the non-TEMPORARY tables, sequences and views in a given MySQL database. We can use the WHERE clause to narrow it to a given type.

We can also use the FULL modifier to return a second column that displays the type:

SHOW FULL TABLES
WHERE Table_Type LIKE 'BASE TABLE'
AND Tables_in_music LIKE 'Albums';

Result:

+-----------------+------------+
| Tables_in_music | Table_type |
+-----------------+------------+
| Albums          | BASE TABLE |
+-----------------+------------+

In this case the database name is music, and so the first column is Tables_in_music.

The SHOW TABLE STATUS Command

In MySQL, the SHOW TABLE STATUS command is similar to the SHOW TABLES command but provides more extensive information about each (non-TEMPORARY) table.

Example:

SHOW TABLE STATUS
FROM Music
WHERE Name = 'Albums';

Result:

+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Name   | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+
| Albums | InnoDB |      10 | Dynamic    |   20 |            819 |       16384 |               0 |        32768 |         0 |             21 | 2021-11-13 12:56:02 | 2021-11-13 12:56:13 | NULL       | utf8mb4_0900_ai_ci |     NULL |                |         |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+

The mysqlshow Client

Another way to check the tables in a MySQL database is to use the mysqlshow client.

To use this utility, open a command line prompt/terminal window and run the following:

mysqlshow --user root --password music;

Be sure to replace music with the database you’re interested in, and root with the applicable user. The --password bit results in the user being prompted for the password.

Result:

Enter password: 
Database: music
+----------------+
|     Tables     |
+----------------+
| Albums         |
| Artists        |
| Genres         |
| valbumsartists |
| valbumsgenres  |
| vallalbums     |
| vallartists    |
| vallgenres     |
+----------------+

The mysqlshow client returns views and tables.

The output displays only the names of those databases, tables, or columns for which the user has some privileges.

If no database is given then all matching databases are shown. If no table is given, then all matching tables in database are shown. If no column is given, then all matching columns and column types in table are shown.

Check if a Table Already Exists Before Creating It

If you need to create the table if it doesn’t exist, you can use the IF NOT EXISTS clause of the CREATE TABLE statement. If the table doesn’t exist, it will be created. If it already exists, it won’t be created.

See How to Check if a Table Already Exists Before Creating It in MySQL for an example.