How to get all Tables list in a MySQL database
Today in this article, we will see how to get all tables listed in a MySQL database.
As a developer or DBA (Database administrator) or tester, it’s important of validating the schema your database supports which includes all the tables and their structure.
Getting a list of tables a database supports is the day-to-day query that most developers or DBA use.
Command
Let us validate the with the most basic command as below. The following query will show all tables in a MySQL database:
SHOW TABLES;
Where to run the above query?
Show table query can be run using any of the below ways,
- MySQL CLI
- MySQL Shell
- MY SQL GUI (like Management studio if any)
- Script (Powershell, Console, C#, Python, or any other language)
Show table using like query
Your database may list 100 of tables in which you may not be interested.
You can query tables using “like” query patterns where you can specify which name pattern you are interested in.
Command
The LIKE the clause below indicates which table names to match and returns all the tables with a matching filter.
SHOW TABLES [LIKE 'pattern' | WHERE expr]
Example
below Command list all table where the table name starts with “TheCodeBuzz_”
SHOW TABLES LIKE 'TheCodeBuzz_%';
Please note below additional guidelines in the above query,
- The tables are ordered in alphabetical order as the default behavior.
- The matching tables list returned also depends on the setting of the
lower_case_table_namessystem variable.
SHOW TABLEScommand lists the non-TEMPORARYtables in a given database.
Do you have any comments or ideas or any better suggestions to share?
Please sound off your comments below.
Happy Coding !!
Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.