To get a list of the tables in a MySQL database, use the 3 client tool to connect to the MySQL server and run the 4 command. Show
Access the MySQL server:
From within the MySQL shell, switch to the database using the 5 statement:
Execute the following command to get a list of all tables and views in the current database:
The output will look something like this:
The optional 6 modifier will show the table type as a second output column.
The output will look something like this:
To get a list of the tables without switching to the database, use either the 7 or 8 clause followed by the database name:
The 9 clause can be used to filter the output of the 4 command according to a specific pattern.
For example, the following statement will return all databases which names starts with ‘open’:
The percent sign ( 1) means zero, one, or multiple characters.Show MySQL Tables from the Command LineTo get information about the tables from the Linux shell, you can use either the 2 command or the 3 command that displays databases and tables information.If we only want base tables to be returned, we can use a 0 clause against the 1 column:
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 3 clause that can be used to narrow the results to just those tables that match a given pattern:
Result: +----------------------+ | Tables_in_music (a%) | +----------------------+ | Albums | | Artists | +----------------------+ In this case I returned all tables that start with the letter 4.The SHOW FULL TABLES;5 CommandThe 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 0 and 3 clause, like +-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+5. Example:
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 TableWe 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 | +-----------------+0 Result: +-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+1 Querying 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 ClientAnother 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 | +-----------------+2 Result: +-----------------+ | Tables_in_music | +-----------------+ | Albums | | Artists | | Genres | | valbumsartists | | valbumsgenres | | vallalbums | | vallartists | | vallgenres | +-----------------+3 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 +-----------------+------------+ | 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 | +-----------------+4 This utility accepts quite a few options, such as 0 (so that you can pass the username), 1 (so that you can pass the password), 2 for the host on which MySQL server is located, etc.
How to check all table status in MySQL?MySQL SHOW TABLES command example
To use the SHOW TABLES command, you need to log on to the MySQL server first. On opening the MySQL Command Line Client, enter your password. Select the specific database. Run the SHOW TABLES command to see all the tables in the database that has been selected.
How to check the status of MySQL database?Checking MySQL status
$ systemctl status mysql ● mysql. service - LSB: Start and stop the mysql database server daemon Loaded: loaded (/etc/init. d/mysql; generated) Active: active (running) since Tue 2021-05-18 13:34:02 CEST; 12s ago Docs: man:systemd-sysv-generator(8) Process: 127538 ExecStart=/etc/init.
How do I show all DBS in MySQL?1. Open the Command Prompt and navigate to the bin folder of your MySQL Server installation directory. Then connect to the server using the mysql -u root -p command. Enter the password and execute the SHOW DATABASES; command we have discussed above.
How to check the health of a table in MySQL?The myisamchk utility is one way to check a table. To invoke this utility, execute the command myisamchk table-file. Because myisamchk requires exclusive access to the tables, a good idea is to take the server offline before running it. This way, you needn't worry about coordinating access between clients.
|