Multiple ways to display the list of tables in MySQL

The tables of a database are frequently listed by developers and DBAs. There are various methods for seeing the tables depending on whether you want to see the table names alone, table names along with their type, or the table names along with their other attributes such as created date, updated date, number of rows in it, data length, etc.

Method # 1:
In the MySQL shell or any GUI, issue the below command to display the list of the tables within the specified database.

SHOW TABLES;
The result will be something like this-

+----------------------------+
| Tables_in_database_name    |
+----------------------------+
| empdept                    |
| tblEmployee                |
| tblEmployee_roles          |
| tblEmployee_salary         |

Method # 2
In order to display the table names and its type, use the below command.

SHOW FULL TABLES;
The result will be something like this-

+----------------------------+------------+
| Tables_in_database_name    | Table_type |
+----------------------------+------------+
| empdept                    | VIEW       |
| tblEmployee                | BASE TABLE |
| tblEmployee_roles          | BASE TABLE |
| tblEmployee_salary         | BASE TABLE |

Method # 3
SELECT * FROM information_schema.TABLES WHERE table_schema = ‘mylocaldb’;
This will return all the metadata about the tables. Their data type, data length, number of rows, table creation date, last modified date, etc.