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.