How to list all tables in a database with MySQLi

How to list all tables in a database with MySQLi

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

How to List All Tables in a Database with MySQLi

You're likely familiar with querying data, but retrieving metadata like table names can sometimes feel like fishing in the dark. The INFORMATION_SCHEMA database is the standard mechanism for this task, and it works seamlessly with the MySQLi extension. While PDO offers a unified interface across drivers, MySQLi provides direct access to specific system tables, allowing you to fetch the list of tables using either procedural or object-oriented styles.

To retrieve the table names programmatically, you execute a SHOW TABLES statement or query the INFORMATION_SCHEMA.TABLES. Both approaches are valid, but they serve slightly different use cases depending on whether you need just the names or additional metadata like engine type or row counts.

Using SHOW TABLES with MySQLi

The most straightforward approach is executing the SHOW TABLES command. This returns a single column containing the names of all user-created tables in the current database context. You don't need to specify the database name in the query itself; MySQLi will use the currently selected database.

Here is how you implement this in procedural PHP:

$conn = new mysqli($host, $user, $password, $database);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Select the database if not already selected
$conn->select_db($database);

$result = $conn->query("SHOW TABLES");

if (!$result) {
    echo "Query error: " . $conn->error;
} else {
    while ($row = $result->fetch_assoc()) {
        echo $row['Tables_in_' . $database] . "<br>";
    }
}

$conn->close();

In the object-oriented style, the syntax remains nearly identical, though you interact with properties like $mysqli->query() and $mysqli->get_result():

$mysqli = new mysqli($host, $user, $password, $database);

if ($mysqli->connect_error) {
    throw new Exception("Connection failed: " . $mysqli->connect_error);
}

$result = $mysqli->query("SHOW TABLES");

if ($result) {
    while ($row = $result->fetch_assoc()) {
        echo htmlspecialchars($row['Tables_in_' . $database]) . "\n";
    }
} else {
    echo "Query failed: " . $mysqli->error;
}

Notice the column name Tables_in_database_name. MySQL dynamically generates this header based on your connection string. If you connect to a database named production_app, the key will be Tables_in_production_app. Forgetting to handle this dynamic key is a common pitfall when iterating over results.

Querying INFORMATION_SCHEMA.TABLES

If you need more than just names—perhaps filtering by engine, checking for temporary tables, or retrieving character set details—you should query the INFORMATION_SCHEMA system database directly.

$query = "SELECT TABLE_NAME 
          FROM INFORMATION_SCHEMA.TABLES 
          WHERE TABLE_SCHEMA = '$database'";

$result = $mysqli->query($query);

This approach is more verbose but offers granular control. You can add WHERE TABLE_TYPE = 'BASE TABLE' to exclude system or temporary tables if your application only cares about persistent data structures. Be cautious with string interpolation in the schema name; always validate and sanitize the $database variable before using it in a raw query to prevent SQL injection vulnerabilities.

Performance Considerations

For most applications, SHOW TABLES is sufficiently fast because it reads from the internal catalog of open connections. However, if you are auditing a massive database with thousands of tables across multiple schemas, querying INFORMATION_SCHEMA.TABLES might be slightly slower due to the larger result set size. In high-concurrency environments, caching the list of tables after an initial load can reduce redundant queries against system tables.

Both methods rely on the server's current configuration and privileges. Ensure your connection user has at least the SHOW VIEW privilege if you intend to inspect views alongside tables, though standard table listing usually requires only SELECT permissions on the schema metadata.

Our network