MySQL - Show Database

MySQL - Listing Databases

The SHOW DATABASES Statement

In MySQL, a database (also called a schema) is a container for your tables, views, and other database objects. To see a list of all databases on the MySQL server that you have permission to see, you use the SHOW DATABASES statement.

Syntax

The syntax is straightforward, with optional clauses for filtering the results:

SHOW DATABASES [LIKE 'pattern' | WHERE expression];

Example

First, let’s create a new database to ensure it appears in our list.

CREATE DATABASE my_project_db;

Now, execute the SHOW DATABASES command:

SHOW DATABASES;

Output

The output is a list of database names. Your list may vary, but it will include system databases and the one you just created.

Database
information_schema
mysql
my_project_db
performance_schema
sys

You can filter this list using the LIKE clause. For example, to find all databases starting with ‘my_’:

SHOW DATABASES LIKE 'my_%';

An Alternative: SHOW SCHEMAS

SCHEMA is a synonym for DATABASE in MySQL. Therefore, the SHOW SCHEMAS command is functionally identical to SHOW DATABASES.

SHOW SCHEMAS;

This command will produce the exact same output as SHOW DATABASES.

The Standard Method: Querying information_schema

A more standardized and flexible way to retrieve metadata about your databases is to query the information_schema. This is a special database that contains information about all other databases. This approach is part of the SQL standard and is more portable across different database systems.

SELECT schema_name FROM information_schema.SCHEMATA;

This query gives you the same list of database names but allows for more complex filtering, ordering, and joining with other metadata tables, providing much greater power than the simple SHOW command.

Listing Databases from Application Code

You can also list databases programmatically from your application. As always, use modern, secure coding practices.

Modern Client Program Examples

PHP
NodeJS
Java
Python



This example uses PDO to execute the `SHOW DATABASES` command and lists the results.

<?php
$host = '127.0.0.1';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

// Note: Connecting without a specific dbname
$dsn = "mysql:host=$host;charset=$charset";
$options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    $stmt = $pdo->query("SHOW DATABASES");

    echo "Available Databases:\n";
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "- " . $row['Database'] . "\n";
    }
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>



Using `mysql2/promise` and `async/await` to list all databases on the server.

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

async function listDatabases() {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'password',
    });

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

    console.log('Available Databases:');
    rows.forEach(row => {
      console.log(`- ${row.Database}`);
    });

  } catch (err) {
    console.error('Error:', err);
  } finally {
    if (connection) await connection.end();
  }
}

listDatabases();



This Java example connects to the MySQL server and uses a `ResultSet` to iterate through the list of databases.

import java.sql.*;

public class ShowDatabases {
    public static void main(String[] args) {
        // Note: The URL does not specify a database name
        String url = "jdbc:mysql://localhost:3306";
        String user = "root";
        String password = "password";

        String sql = "SHOW DATABASES";

        try (Connection con = DriverManager.getConnection(url, user, password);
             PreparedStatement pst = con.prepareStatement(sql);
             ResultSet rs = pst.executeQuery()) {

            System.out.println("Available Databases:");
            while (rs.next()) {
                System.out.println("- " + rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}



This Python script connects to the MySQL server, executes `SHOW DATABASES`, and prints the name of each database.

import mysql.connector
from mysql.connector import Error

def list_databases():
    connection = None
    try:
        # Note: Do not specify a 'database' on initial connection
        connection = mysql.connector.connect(
            host='localhost',
            user='root',
            password='password'
        )
        cursor = connection.cursor()
        cursor.execute("SHOW DATABASES")

        print("Available Databases:")
        for db in cursor:
            print(f"- {db[0]}")

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

if __name__ == '__main__':
    list_databases();

Understanding Permissions

It’s important to remember that SHOW DATABASES only lists the databases for which the current user has some form of privilege. An administrative user (like root) will see all databases, but a restricted user will only see the ones they are explicitly granted access to. This is a fundamental security feature of MySQL.