How to list tables in MySQL database
Sep 11th, 2021,Here are a couple of ways to list all tables in a MySQL database. Here we have included some possible ways.
1. Using SQL Query
To show all tables in the current MySQL database, you can use the following script:
SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema = 'your_database’
AND table_schema not in ('information_schema','mysql', 'performance_schema','sys')
ORDER BY table_schema, table_name;
Sample results
As a result, you will get the list of all tables as shown in the picture below:
2. List all tables with MySQL interactive shell command-line
If you Prefer using the MySQL interactive shell command line, the following command would help show full tables.
All you need to do is connect to your database with the use your_database_name command, for example: use sakila.
After that, run the show full tables command. As can be seen, this command shows all the tables and views.
With an expression in the where clause in the show tables command, you can filter the result to list only the tables using this statement show full tables where tables_type = ‘BASE TABLE’;
3. Display all table in MySQL database with ERBuilder Data Modeler
Finally, we'll have a look at ERBuilder Data Modeler , which can extract database data structure. In addition to display all tables as a list (shown in red in the picture below), you can also see them as an ER Diagram (shown in blue). Furthermore, you can use the data model browser module which allows you to quickly explore and browse any table you desire (shown in green).
Furthermore, with the use of ERBuilder Data Modeler, you can also view details of the table and edit it. Simply by just double-clicking on the table name as you can see from the picture below, you will be getting table's columns, indexes, constraints, keys, triggers, description, and the SQL script.
How ERBuilder Helps
ERBuilder Data Modeler is a GUI data modeling tool that allows you to visualize, design and model databases by using entity relationship diagrams and automatically generates the most popular SQL databases including Amazon Redshift. Generate and share the data Model documentation with your team. Optimize your data model by using advanced features such as test data generation, schema compare and schema synchronization. Try now ERBuilder for 15 days FREE
Subscribe To Our Newsletter
Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!
