MySQL Show Databases

Last Updated: 11 July, 2023

To display a list of all databases in a MySQL server, we can use the SHOW DATABASES statement in the MySQL shell. The output of the statement is a list of database names, one per line.

The following is an example of the output of the SHOW DATABASES statement:

  SHOW DATABASES;

This command will list all the databases that exist on the MySQL server we are currently connected to.

The following is an example of the output of the SHOW DATABASES statement:

  mysql> SHOW databases;
  +--------------------+
  | Database           |
  +--------------------+
  | employeesdb        |
  | information_schema |
  | mysql              |
  | performance_schema |
  | schooldb           |
  | sys                |
  +--------------------+
  6 rows in set (0.00 sec)

Show Databases Using Pattern Matching

The SHOW DATABASES statement can also be used to filter the list of databases by name. If we want to query the database that matches a specific pattern, we use the LIKE clause as follows:

  SHOW DATABASES LIKE pattern;

Example: following statement would list all the databases that start with the letter s:

  mysql> SHOW DATABASES LIKE 's%';
  +---------------+
  | Database (s%) |
  +---------------+
  | schooldb      |
  | sys           |
  +---------------+
  2 rows in set (0.07 sec)

Example: following statement returns database that ends with the string 'schema';

  mysql> SHOW DATABASES LIKE '%schema';
  +--------------------+
  | Database (%schema) |
  +--------------------+
  | information_schema |
  | performance_schema |
  +--------------------+
  2 rows in set (0.17 sec)

The SHOW DATABASES statement is a very useful tool for getting a list of all the databases on a MySQL server. It can be used to quickly identify which databases exist, and to filter the list of databases by name.