List All The Tables In Mysql

6 min read

Knowing how to list all the tables in MySQL is one of the first skills every database administrator, backend developer, and data analyst needs to master. So naturally, whether you are exploring a new database for the first time, debugging an application, or documenting a schema, being able to quickly view every table gives you a clear map of how your data is organized. This guide walks you through every reliable method to list tables in MySQL, from the simplest one-line command to advanced queries that filter and format results exactly the way you need.

Why Listing Tables Matters

Before diving into the commands, it helps to understand why this operation is so common. Think about it: a MySQL database is essentially a structured container that holds tables, and each table stores rows of related data. When you connect to an unfamiliar database, your first instinct is usually to see what is inside Practical, not theoretical..

  • Understand the overall structure of a schema before writing queries.
  • Confirm that a migration or import completed successfully.
  • Locate a specific table among hundreds in a large system.
  • Generate documentation for developers and stakeholders.

Because MySQL offers several ways to accomplish this, choosing the right method depends on your environment and how much detail you need.

Method 1: The SHOW TABLES Command

The most direct way to list all the tables in MySQL is the SHOW TABLES statement. It is short, fast, and works in almost every version of MySQL and MariaDB.

SHOW TABLES;

The moment you run this, MySQL returns a single column named Tables_in_<database_name> containing every table in the currently selected database. Take this: if your database is called school, the output header would read Tables_in_school.

A few important points to remember:

  1. You must first select a database using USE database_name; before running SHOW TABLES;, otherwise MySQL will not know which schema to inspect.
  2. The command only shows tables, not views, unless you use a variant described below.
  3. It is case-sensitive on some operating systems, particularly Linux, so table naming conventions matter.

Method 2: Using SHOW FULL TABLES for Extra Detail

If you want more than just names, the SHOW FULL TABLES command adds a second column that identifies whether each entry is a base table or a view.

SHOW FULL TABLES;

The output includes two columns: Tables_in_<database_name> and Table_type. This is especially useful in databases that mix regular tables with views, because it lets you distinguish between the two at a glance. When you are documenting a schema or auditing an application, this extra context can save you considerable time.

Method 3: Filtering Tables with the LIKE Clause

In large databases with hundreds of tables, scrolling through the entire list is inefficient. MySQL lets you narrow the results using the LIKE operator directly within the SHOW TABLES statement And that's really what it comes down to..

SHOW TABLES LIKE 'user%';

This returns only the tables whose names begin with user, such as users, user_roles, and user_sessions. The % symbol acts as a wildcard that matches any sequence of characters. You can also use _ to match a single character. This filtering technique is one of the most practical tools for developers who already know roughly what they are looking for Small thing, real impact..

Method 4: Querying INFORMATION_SCHEMA

For maximum flexibility and control, the INFORMATION_SCHEMA database is the most powerful approach. That said, mySQL stores metadata about every database, table, and column in this special system schema. To list all tables, you query the TABLES table within it.

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'your_database_name';

This method shines because you can apply any SQL logic you want. To give you an idea, you can:

  • Filter by table type using WHERE table_type = 'BASE TABLE'.
  • Sort results alphabetically with ORDER BY table_name;.
  • Combine conditions to find tables created after a certain date.
  • Count the total number of tables with SELECT COUNT(*).

Here is a more detailed example that returns both the table name and its type:

SELECT table_name, table_type 
FROM information_schema.tables 
WHERE table_schema = 'school' 
ORDER BY table_name;

Because INFORMATION_SCHEMA is standard across MySQL versions, this approach is highly reliable and portable.

Method 5: Listing Tables from the Command Line

If you prefer working in the terminal rather than inside a SQL client, you can list tables directly from the MySQL command-line client. After logging in, the process is the same as above:

mysql -u username -p

Once connected, run:

USE school;
SHOW TABLES;

Alternatively, you can execute a single command without entering the interactive shell:

mysql -u username -p -e "SHOW TABLES;" school

This one-liner is convenient for scripts, automation, and quick checks when you already know the database name That's the whole idea..

Comparing the Methods

Each approach has its strengths, and the right choice depends on your goal:

  • SHOW TABLES — fastest for a quick, simple list.
  • SHOW FULL TABLES — best when you need to separate tables from views.
  • LIKE filtering — ideal for narrowing down results in large schemas.
  • INFORMATION_SCHEMA — most flexible for complex queries and reporting.
  • Command line — perfect for automation and terminal-based workflows.

Frequently Asked Questions

How do I list tables in a specific database? First select the database with USE database_name;, then run SHOW TABLES;. Alternatively, query INFORMATION_SCHEMA.TABLES and filter by table_schema.

Can I see tables from all databases at once? Yes. Using INFORMATION_SCHEMA, you can query without filtering by table_schema to see tables across every database, though the results can be large Less friction, more output..

Why does SHOW TABLES return nothing? This usually means no database is selected, or the current database genuinely has no tables. Make sure you ran USE database_name; first.

Does SHOW TABLES include views? The basic SHOW TABLES command may include views depending on your version. To clearly separate them, use SHOW FULL TABLES and check the Table_type column And that's really what it comes down to. Worth knowing..

Is there a difference between MySQL and MariaDB? For listing tables, the commands are essentially identical, so anything covered here works in both systems.

Conclusion

Listing all the tables in MySQL is a foundational skill that supports everything from routine maintenance to complex database development. Day to day, the simple SHOW TABLES command covers most everyday needs, while SHOW FULL TABLES, the LIKE clause, and the powerful INFORMATION_SCHEMA queries give you the precision required for advanced work. On top of that, by understanding when to use each method, you can handle any MySQL database with confidence, quickly find the tables you need, and build a clear picture of how your data is structured. Start with the basics, experiment with filtering, and gradually incorporate INFORMATION_SCHEMA into your workflow as your needs grow more sophisticated Still holds up..

And yeah — that's actually more nuanced than it sounds.

Freshly Written

Latest Batch

Related Territory

People Also Read

Thank you for reading about List All The Tables In Mysql. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home