Introduction

If you want to count how many tables exist in a MySQL database, the standard solution is to query information_schema.tables. That system schema contains metadata about databases, tables, views, and other objects managed by the server.

The important detail is that information_schema.tables contains entries for every schema on the server, so your query must filter by the database name you care about.

Count Base Tables in One Database

The most common query is:

1SELECT COUNT(*) AS table_count
2FROM information_schema.tables
3WHERE table_schema = 'your_database_name'
4  AND table_type = 'BASE TABLE';

This counts only normal tables in the named schema.

Why include table_type = 'BASE TABLE'? Because information_schema.tables can also include views. If you want a pure table count, exclude them explicitly.

Count Tables in the Current Database

If you are already connected to the database and do not want to hard-code its name, use DATABASE().

1SELECT COUNT(*) AS table_count
2FROM information_schema.tables
3WHERE table_schema = DATABASE()
4  AND table_type = 'BASE TABLE';

That makes the query more reusable in scripts and admin sessions.

Include Views If That Matches Your Goal

Sometimes the real question is “how many table-like objects do I have?” In that case, remove the table_type filter.

SELECT COUNT(*) AS object_count
FROM information_schema.tables
WHERE table_schema = DATABASE();

That will count both base tables and views.

If you want to see the breakdown instead of a single number:

1SELECT table_type, COUNT(*) AS count_per_type
2FROM information_schema.tables
3WHERE table_schema = DATABASE()
4GROUP BY table_type;

This is often the most informative version because it shows exactly what MySQL is counting.

List the Tables Too

A count is useful, but during troubleshooting you often want the actual names as well.

1SELECT table_name
2FROM information_schema.tables
3WHERE table_schema = DATABASE()
4  AND table_type = 'BASE TABLE'
5ORDER BY table_name;

That lets you verify the count and spot unexpected objects.

Why Not Use SHOW TABLES?

SHOW TABLES is fine for interactive use, but it is less convenient when you want a count directly in SQL logic.

SHOW TABLES;

This prints a result set of names, which your client can count, but information_schema is better when you want filtering, grouping, joins, or scripting.

Performance and Permissions

For normal database sizes, this metadata query is lightweight. The more common issue is permissions. If a user sees fewer schemas or objects than an admin user, the count may differ because metadata visibility is permission-sensitive.

That is not a bug in the query. It reflects the database account’s visibility into server objects.

This is worth remembering in shared environments. A deployment script run by an application account may report a smaller table count than the same query run by a DBA account, even though both are connected to the same server.

Common Pitfalls

• Forgetting to filter by table_schema, which counts objects from other databases too.
• Counting views unintentionally because information_schema.tables includes more than just base tables.
• Hard-coding a schema name when DATABASE() would make the query reusable.
• Comparing counts from different users without considering metadata permissions.
• Using SHOW TABLES when you actually need a SQL expression that can be filtered or grouped.

Summary

• Use information_schema.tables to count MySQL tables reliably.
• Filter by table_schema so you only count objects in the target database.
• Add table_type = 'BASE TABLE' when you want tables but not views.
• Use DATABASE() if you want the current schema dynamically.
• Querying metadata is usually better than SHOW TABLES when you need a count in SQL.