How to get all Tables list in a MySQL database

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_names system variable.
  • SHOW TABLES command lists the non-TEMPORARY tables 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.



Leave a Reply

Your email address will not be published. Required fields are marked *