How can I check the list of MySQL tables, in the current database we are using, along with table type in the result set?

MySQLMySQLi Database

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.

raja
Published on 20-Feb-2018 18:30:00
Advertisements