MySQL - Show Tables

MySQL: Listing and Inspecting Tables

As a developer or database administrator, you frequently need to list the tables within a database. This is essential for understanding the database schema, debugging, or writing dynamic scripts. MySQL provides two primary methods for this: the convenient SHOW TABLES command and the more powerful, standard INFORMATION_SCHEMA.

Method 1: The SHOW TABLES Statement

SHOW TABLES is a MySQL-specific command that provides a quick and easy way to see all non-temporary tables, views, or sequences in the current database.

Basic Syntax

To use it, first select a database, then run the command.

USE my_database;
SHOW TABLES;

Example

Let’s assume we have a database named company_db with a few tables.

USE company_db;

Executing SHOW TABLES; would produce a result like this:

Tables_in_company_db
departments
employees
projects
salaries

Advanced SHOW TABLES Usage

The command has several useful modifiers:

• Listing from another database: SHOW TABLES FROM another_db; or SHOW TABLES IN another_db;.
• Filtering with LIKE: Use wildcard characters (% for any characters, _ for a single character) to find tables matching a pattern. SHOW TABLES LIKE 'proj%'; would list tables starting with ‘proj’.
• Filtering with WHERE: You can apply a WHERE clause to the output. SHOW TABLES WHERE Tables_in_company_db = 'employees';.
• Getting more information with FULL: SHOW FULL TABLES; adds a Table_type column, which shows if an object is a BASE TABLE or a VIEW.

-- Example using FULL modifier
SHOW FULL TABLES;

-- Output would look like:
-- +-----------------------+------------+
-- | Tables_in_company_db  | Table_type |
-- +-----------------------+------------+
-- | departments           | BASE TABLE |
-- | employees             | BASE TABLE |
-- | projects              | BASE TABLE |
-- | salaries              | BASE TABLE |
-- +-----------------------+------------+

Method 2: The INFORMATION_SCHEMA

The INFORMATION_SCHEMA is a standard, read-only database that exists in all modern SQL databases. It contains metadata about the database system itself, including information about databases, tables, columns, and permissions. Querying it is the most flexible and SQL-standard way to get schema information.

Best Practice: For application code or complex scripts that need to inspect the database schema, prefer using INFORMATION_SCHEMA. It’s more portable across different SQL databases and more powerful than the SHOW command.

Syntax

To list tables, you query the TABLES table within INFORMATION_SCHEMA.

SELECT table_name, table_type
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'company_db';

This gives you the same information as SHOW FULL TABLES, but with the full power of SELECT statements. You can add more complex WHERE clauses, JOIN with other metadata tables (like COLUMNS), and order the results.

Example: Finding tables with a specific column

SELECT DISTINCT T.table_name
FROM INFORMATION_SCHEMA.TABLES AS T
JOIN INFORMATION_SCHEMA.COLUMNS AS C
    ON T.table_schema = C.table_schema AND T.table_name = C.table_name
WHERE T.table_schema = 'company_db' AND C.column_name = 'employee_id';

Listing Tables Using Client Programs

Here are modern examples of how to fetch and display a list of tables from a specific database using various programming languages.

Python
Node.js
Java
PHP



This Python example uses `mysql-connector-python` and queries the `INFORMATION_SCHEMA` for a robust solution.

```python
import mysql.connector
import os

def list_tables_in_database(db_name):
    """Lists all base tables in a given database."""
    connection = None
    try:
        connection = mysql.connector.connect(
            host=os.getenv('DB_HOST', 'localhost'),
            user=os.getenv('DB_USER', 'root'),
            password=os.getenv('DB_PASSWORD', 'password'),
            database=db_name
        )
        cursor = connection.cursor()

        # Using the standard INFORMATION_SCHEMA is a best practice.
        query = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = %s AND table_type = 'BASE TABLE'"
        cursor.execute(query, (db_name,))

        print(f"Tables in database '{db_name}':")
        tables = [item[0] for item in cursor.fetchall()]
        if tables:
            for table_name in tables:
                print(f"- {table_name}")
        else:
            print("No tables found.")

    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

# --- Run the example ---
list_tables_in_database('company_db')

Output:

Tables in database 'company_db':
- departments
- employees
- projects
- salaries

This Node.js example uses mysql2/promise with async/await and the simple SHOW TABLES command.

const mysql = require('mysql2/promise');

async function listTables(dbName) {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: process.env.DB_HOST || 'localhost',
            user: process.env.DB_USER || 'root',
            password: process.env.DB_PASSWORD || 'password',
            database: dbName
        });

        const [rows] = await connection.execute('SHOW TABLES');

        console.log(`Tables in database '${dbName}':`);
        if (rows.length > 0) {
            // The column name is 'Tables_in_[dbName]'
            const key = Object.keys(rows[0])[0];
            rows.forEach(row => {
                console.log(`- ${row[key]}`);
            });
        } else {
            console.log('No tables found.');
        }

    } catch (error) {
        console.error(`Database error: ${error.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

// --- Run the example ---
listTables('company_db');

Output:

Tables in database 'company_db':
- departments
- employees
- projects
- salaries

This Java example uses modern JDBC with try-with-resources. It retrieves table metadata without executing a manual query.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ShowTablesExample {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/company_db";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";

    public static void main(String[] args) {
        // try-with-resources ensures the connection and resultset are closed
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            DatabaseMetaData metaData = conn.getMetaData();

            // Get tables for the current catalog (database), schema, no specific name pattern, and only 'TABLE' type
            try (ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"})) {
                System.out.println("Tables in database 'company_db':");
                while (tables.next()) {
                    String tableName = tables.getString("TABLE_NAME");
                    System.out.println("- " + tableName);
                }
            }
        } catch (SQLException e) {
            System.err.println("SQL Error: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Output:

Tables in database 'company_db':
- departments
- employees
- projects
- salaries

This PHP example uses the mysqli extension with an object-oriented style and robust error handling.

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'company_db';

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

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

    echo "Tables in database '$dbname':\n";
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_array()) {
            echo "- " . $row[0] . "\n";
        }
    } else {
        echo "No tables found.\n";
    }

    $result->free();
    $mysqli->close();

} catch (mysqli_sql_exception $e) {
    echo "Database Error: " . $e->getMessage() . "\n";
    exit();
}

?>

Output:

Tables in database 'company_db':
- departments
- employees
- projects
- salaries