Below are four ways to list out the tables in a MySQL database using SQL or the command line.
The SHOW TABLES
Command
The SHOW TABLES
command lists the non-TEMPORARY
tables and views in a given database:
SHOW TABLES;
Result:
+-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+
That listed all tables in the current database, which is called Music
.
We can also use the FULL
modifier to return a second column that displays the type:
SHOW FULL TABLES;
Result:
+-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | | valbumsartists | VIEW | | valbumsgenres | VIEW | | vallalbums | VIEW | | vallartists | VIEW | | vallgenres | VIEW | +-----------------+------------+
We can see that most of the results listed here are actually views.
If we only want base tables to be returned, we can use a WHERE
clause against the Table_type
column:
SHOW FULL TABLES
WHERE Table_type = 'BASE TABLE';
Result:
+-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | +-----------------+------------+
SHOW TABLES
also accepts a LIKE
clause that can be used to narrow the results to just those tables that match a given pattern:
SHOW TABLES
LIKE 'a%';
Result:
+----------------------+ | Tables_in_music (a%) | +----------------------+ | Albums | | Artists | +----------------------+
In this case I returned all tables that start with the letter a
.
The SHOW TABLE STATUS
Command
The SHOW TABLE STATUS
command is similar to the SHOW TABLES
command but provides more extensive information about each (non-TEMPORARY
) table.
It also accepts a WHERE
and LIKE
clause, like SHOW TABLES
.
Example:
SHOW TABLE STATUS
LIKE 'a%';
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 | 2022-02-15 09:10:36 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | | | Artists | InnoDB | 10 | Dynamic | 16 | 1024 | 16384 | 0 | 0 | 0 | 17 | 2021-11-13 12:56:02 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | | +---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
The information_schema.TABLES
Table
We can also query the information_schema.TABLES
table:
SELECT
TABLE_NAME
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA LIKE 'Music' AND TABLE_TYPE LIKE 'BASE_TABLE';
Result:
+------------+ | TABLE_NAME | +------------+ | Artists | | Genres | | Albums | +------------+
Querying this without filtering the results by TABLE_SCHEMA
returns tables from all databases. Similarly, querying it without filtering by TABLE_TYPE
returns all table types.
The mysqlshow
Client
Another way to do it is with the mysqlshow
utility.
To use this option, open a command line prompt/terminal window and run the following (replacing music
with the database you’re interested in):
mysqlshow music;
Result:
+----------------+ | Tables | +----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +----------------+
This returns views and tables.
The output displays only the names of those databases, tables, or columns for which you have some privileges.
If no database is given, a list of database names is shown. If no table is given, all matching tables in the database are shown. If no column is given, all matching columns and column types in the table are shown.
If you get an “access denied” error, you may need to use sudo
, and enter your password at the prompt:
sudo mysqlshow music;
This utility accepts quite a few options, such as --user
(so that you can pass the username), --password
(so that you can pass the password), --host
for the host on which MySQL server is located, etc.
See MySQL’s documentation for a full list of options.