SHOW DATABASES - MySQL

Overview

The SHOW DATABASES command displays a list of all databases that the current MySQL user has access to. It provides a quick overview of available databases, helping users navigate and manage their database environments.

Syntax

SHOW DATABASES [LIKE 'pattern' | WHERE expr] [ORDER BY identifier]

Options/Flags

  • LIKE ‘pattern’: Filters results to databases matching the specified pattern (e.g., SHOW DATABASES LIKE 'prod%').
  • WHERE expr: Filters results based on a condition. The condition can use comparison operators (=, !=, <, >, <=, >=) and logical operators (AND, OR).
  • ORDER BY identifier: Orders the result set by a specified identifier (e.g., SHOW DATABASES ORDER BY name).

Examples

Simple Usage:

Display all databases:

SHOW DATABASES;

Filter by Pattern:

Show databases with names starting with “prod”:

SHOW DATABASES LIKE 'prod%';

Filter by Condition:

Show databases with more than 100 tables:

SHOW DATABASES WHERE tables > 100;

Common Issues

  • Permission Denied: Ensure the current user has the necessary privileges to view all databases.
  • Malformed Condition: Ensure the WHERE clause is syntactically correct.

Integration

The SHOW DATABASES command can be used in conjunction with other MySQL commands to manage databases more efficiently. For example:

SHOW DATABASES;
USE <database_name>;
  • USE: Selects a specific database for use.
  • CREATE DATABASE: Creates a new database.
  • DROP DATABASE: Deletes a database.