MYSQL - SHOW SCHEMAS statement

The SHOW SCHEMAS statement

The SHOW SCHEMAS is a synonym for the SHOW DATABASES statement so, you can also use this statement to list out databases.

Example

Assume we have created the deleted databases again, using the CREATE DATABASE statement as −

mysql> CREATE DATABASE testDB1;
Query OK, 1 row affected (0.34 sec)

mysql> CREATE DATABASE testDB2;
Query OK, 1 row affected (0.19 sec)

mysql> CREATE DATABASE testDB3;
Query OK, 1 row affected (0.21 sec)

Following SHOW SCHEMAS statement lists out the databases −

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydatabase         |
| performance_schema |
| testdb1            |
| testdb2            |
| testdb3            |
| testdb4            |
| world              |
+--------------------+
8 rows in set (0.00 sec)

The LIKE clause

Using the LIKE clause, you can specify a pattern to retrieve specific databases. Following query retrieves the database name starting with the word "test".

mysql> SHOW DATABASES LIKE 'test%';
+------------------+
| Database (test%) |
+------------------+
| testdb1          |
| testdb2          |
| testdb3          |
| testdb4          |
+------------------+
4 rows in set (0.00 sec)

The WHERE clause

You can use the WHERE clause of the SHOW DATABASES statements to retrieve names of the databases which match the specified condition.

mysql> SHOW DATABASES WHERE `Database` LIKE '%test%';
+------------------+
| Database         |
+------------------+
| testdb1          |
| testdb2          |
| testdb3          |
| testdb4          |
+------------------+
4 rows in set (0.00 sec)