To get a list of the tables in a MySQL database, use the USE database_name;3 client tool to connect to the MySQL server and run the USE database_name;4 command.
Access the MySQL server:
mysql -u user -pFrom within the MySQL shell, switch to the database using the USE database_name;5 statement:
USE database_name;Execute the following command to get a list of all tables and views in the current database:
SHOW TABLES;The output will look something like this:
+----------------------------+ | Tables_in_database_name | +----------------------------+ | actions | | permissions | | permissions_roles | | permissions_users | | roles | | roles_users | | settings | | users | +----------------------------+ 8 rows in set (0.00 sec)The optional USE database_name;6 modifier will show the table type as a second output column.
SHOW FULL TABLES;The output will look something like this:
+----------------------------+------------+ | Tables_in_database_name | Table_type | +----------------------------+------------+ | actions | VIEW | | permissions | BASE TABLE | | permissions_roles | BASE TABLE | | permissions_users | BASE TABLE | | roles | BASE TABLE | | roles_users | BASE TABLE | | settings | BASE TABLE | | users | BASE TABLE | +----------------------------+------------+ 8 rows in set (0.00 sec)To get a list of the tables without switching to the database, use either the USE database_name;7 or USE database_name;8 clause followed by the database name:
The USE database_name;9 clause can be used to filter the output of the USE database_name;4 command according to a specific pattern.
SHOW TABLES LIKE pattern;For example, the following statement will return all databases which names starts with ‘open’:
SHOW TABLES LIKE 'permissions%';+-------------------------------------------+ | Tables_in_database_name (permissions%) | +-------------------------------------------+ | permissions | | permissions_roles | | permissions_users | +-------------------------------------------+ 3 rows in set (0.00 sec)The percent sign (SHOW TABLES;1) means zero, one, or multiple characters.
Show MySQL Tables from the Command Line
To get information about the tables from the Linux shell, you can use either the SHOW TABLES;2 command or the SHOW TABLES;3 command that displays databases and tables information.
If we only want base tables to be returned, we can use a SHOW FULL TABLES;0 clause against the SHOW FULL TABLES;1 column:
SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';Result:
+-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | +-----------------+------------++-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+5 also accepts a SHOW FULL TABLES;3 clause that can be used to narrow the results to just those tables that match a given pattern:
SHOW TABLES LIKE 'a%';Result:
In this case I returned all tables that start with the letter SHOW FULL TABLES;4.
The SHOW FULL TABLES;5 Command
The SHOW FULL TABLES;5 command is similar to the +-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+5 command but provides more extensive information about each (non-+-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+7) table.
It also accepts a SHOW FULL TABLES;0 and SHOW FULL TABLES;3 clause, like +-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+5.
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 +-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | | valbumsartists | VIEW | | valbumsgenres | VIEW | | vallalbums | VIEW | | vallartists | VIEW | | vallgenres | VIEW | +-----------------+------------+2 Table
We can also query the +-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | | valbumsartists | VIEW | | valbumsgenres | VIEW | | vallalbums | VIEW | | vallartists | VIEW | | vallgenres | VIEW | +-----------------+------------+2 table:
+-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+0Result:
+-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+1Querying this without filtering the results by +-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | | valbumsartists | VIEW | | valbumsgenres | VIEW | | vallalbums | VIEW | | vallartists | VIEW | | vallgenres | VIEW | +-----------------+------------+4 returns tables from all databases. Similarly, querying it without filtering by +-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | | valbumsartists | VIEW | | valbumsgenres | VIEW | | vallalbums | VIEW | | vallartists | VIEW | | vallgenres | VIEW | +-----------------+------------+5 returns all table types.
The +-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | | valbumsartists | VIEW | | valbumsgenres | VIEW | | vallalbums | VIEW | | vallartists | VIEW | | vallgenres | VIEW | +-----------------+------------+6 Client
Another way to do it is with the +-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | | valbumsartists | VIEW | | valbumsgenres | VIEW | | vallalbums | VIEW | | vallartists | VIEW | | vallgenres | VIEW | +-----------------+------------+6 utility.
To use this option, open a command line prompt/terminal window and run the following (replacing +-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | | valbumsartists | VIEW | | valbumsgenres | VIEW | | vallalbums | VIEW | | vallartists | VIEW | | vallgenres | VIEW | +-----------------+------------+8 with the database you’re interested in):
+-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+2Result:
+-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+3This 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 +-----------------+------------+ | Tables_in_music | Table_type | +-----------------+------------+ | Albums | BASE TABLE | | Artists | BASE TABLE | | Genres | BASE TABLE | | valbumsartists | VIEW | | valbumsgenres | VIEW | | vallalbums | VIEW | | vallartists | VIEW | | vallgenres | VIEW | +-----------------+------------+9, and enter your password at the prompt:
+-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+4This utility accepts quite a few options, such as SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';0 (so that you can pass the username), SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';1 (so that you can pass the password), SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';2 for the host on which MySQL server is located, etc.