Check MySQL Tables in Current Database with Table Type

It can be done with the SHOW FULL TABLES statement. Its Syntax would be as follows −

Syntax

SHOW FULL TABLES

Example

In the following example our current database is ‘query’ hence the statement below will show us the table list along with table type in the result set from this database −

mysql> SHOW FULL TABLES;
+-----------------------------+------------+
| Tables_in_query             | Table_type |
+-----------------------------+------------+
| accounts                    | BASE TABLE |
| address                     | BASE TABLE |
| cars                        | BASE TABLE |
| cars_avgprice               | VIEW       |
| countries                   | BASE TABLE |
| customer_view               | VIEW       |
| customers                   | BASE TABLE |
| date_time_test              | BASE TABLE |
| detail_bday                 | BASE TABLE |
| details_city                | BASE TABLE |
.
.
.
| view_student_detail         | VIEW       |
| view_student_detail_columns | VIEW       |
| websites                    | BASE TABLE |
+-----------------------------+------------+
87 rows in set (0.01 sec)

The above results set show the tables along with table type i.e. whether it is a BASE TABLE or VIEW.