To list tables, use the SHOW TABLES command. If no database has been selected yet, the user must first run USE database_name. This is the quickest way to view tables through an SQL tool that supports MySQL syntax, via SSH, or directly in the MySQL client.

Method 1 — the SHOW TABLES command

To use this command, select a database first. If you're not already connected to the MySQL console, connect, then run:

USE database_name;
SHOW TABLES;

In practice, the command looks like this:

USE shop;
SHOW TABLES;

Running this command will produce output similar to this:

  • customers;

  • orders

  • products;

  • order_items.

The actual result depends entirely on the specific table and the data it contains. If you'd rather not switch databases, you can specify it directly in the command:

SHOW TABLES FROM shop;

This is handy when a server hosts several databases and you need to quickly check a specific one.

How to find tables by name

Use LIKE for filtering:

SHOW TABLES LIKE 'user%';

For example, this command finds tables whose names start with "user":

users
user_settings
user_sessions

The % wildcard matches any number of characters, while "_" matches a single character. LIKE is supported for filtering SHOW TABLES output.

These commands are run directly in the mysql client after connecting to the server:

mysql -u root -p

After entering the password, an SQL console opens where you can run USE, SHOW TABLES, and other commands. If the database is hosted on a VPS, console access is usually available via SSH. Leneex users can connect to their server via SSH and manage their installed MySQL instances independently.

Important: SHOW DATABASES and SHOW TABLES are different commands. The former lists the available databases, while the latter lists the tables inside a specific database.

Method 2 — querying INFORMATION_SCHEMA

With another simple command, you can get not just a list of table names but also additional information about the tables. This is done using INFORMATION_SCHEMA.TABLES.

The simplest query looks like this:

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

This command returns a list of tables in the shop database. INFORMATION_SCHEMA provides metadata about databases and MySQL objects, including tables. INFORMATION_SCHEMA.TABLES exposes details such as the table name, object type, storage engine used, row count, and size.

For example, you can retrieve the table type and storage engine right away:

SELECT
table_name,
table_type,
engine
FROM information_schema.tables
WHERE table_schema = 'shop';

You can also get an approximate size for each table:

SELECT
table_name,
engine,
table_rows,
data_length,
index_length
FROM information_schema.tables
WHERE table_schema = 'shop';

This approach is especially useful when you need to run a quick database audit:

  • identify the storage engine used by each table;

  • review the estimated row counts;

  • find the largest objects.

SHOW TABLES is better suited for the simple "show me the names" task, while INFORMATION_SCHEMA.TABLES is the way to go when you need metadata alongside the list. In MySQL, INFORMATION_SCHEMA serves as a system interface for retrieving information about the server's structure and objects.

Method 3 — via phpMyAdmin or Adminer

If working with the command line isn't convenient, or you don't have console access, you can view the list of tables through a web-based database management interface — for example, phpMyAdmin or Adminer.

Here's how it works:

  1. Open the database management tool available on the server.

  2. Select the needed database from the list.

  3. Open the page or section for that database.

  4. Review the list of tables.

  5. If needed, select a specific table to view its structure, data, indexes, and other parameters.

This is convenient for anyone working with a database through a hosting control panel rather than SSH. The exact interface will differ depending on the server configuration and the control panel in use. In cPanel, Plesk, DirectAdmin, FASTPANEL, or HestiaCP, the path to the database won't be the same. The specific tool — phpMyAdmin, Adminer, or another solution — also depends on the installed panel and its configuration.

How to get a list of tables along with row counts

When you need more than just table names and also want to see the row counts, you can use INFORMATION_SCHEMA.TABLES:

SELECT
table_name,
table_rows
FROM information_schema.tables
WHERE table_schema = 'shop';

Note that TABLE_ROWS may be an estimated value rather than an exact row count for some engines, including InnoDB. If you need the exact number of records for a specific table, use a separate query:

SELECT COUNT(*) FROM orders;

For a large number of tables, this approach can be noticeably more resource-intensive, so for a general overview of the database, INFORMATION_SCHEMA metadata is usually the preferred choice.

Conclusion

If you need to quickly get a list of tables through the console, use: SHOW TABLES; If no database is selected: SHOW TABLES FROM database_name; When you need additional details — such as table type, engine, or size — it's more convenient to query INFORMATION_SCHEMA.TABLES.

If you don't have command-line access, the same task can be accomplished through phpMyAdmin, Adminer, or another web-based database management interface. For a typical administrative task, the choice is straightforward: quick viewing — SHOW TABLES; metadata analysis — INFORMATION_SCHEMA; working without a console — a graphical control panel.