MySQL SHOW DATABASES: List All Databases in MySQL

MySQL SHOW DATABASES Command

The SHOW DATABASES command in MySQL is used to list all the databases available on the MySQL server. It helps you to view the existing databases and ensure their availability.

Syntax

SHOW DATABASES;

Key Points

1. Access Control:

   • The list of databases displayed depends on the privileges of the connected MySQL user.
   • A user can only see databases for which they have access.

2. Default Databases:

   • information_schema: Stores metadata about all other databases.
   • mysql: Contains MySQL system tables.
   • performance_schema: Contains information about server performance.
   • sys: Provides views and functions to simplify server performance monitoring.

Examples

1. List All Databases

SHOW DATABASES;

Example Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_database        |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2. Filter Databases Using LIKE

To list databases with a specific pattern, use the LIKE clause:

SHOW DATABASES LIKE 'my%';

Example Output:

+----------------+
| Database       |
+----------------+
| my_database    |
| my_other_db    |
+----------------+

3. Filter Databases Using WHERE (Information Schema)

If you need advanced filtering, query the SCHEMATA table in the information_schema database:

SELECT schema_name 
FROM information_schema.schemata
WHERE schema_name LIKE 'test%';

Example Output:

+--------------+
| schema_name  |
+--------------+
| test_db      |
| test_schema  |
+--------------+

Checking Database Details

1. Show the Default Character Set and Collation

To check the character set and collation of each database:

SELECT schema_name, default_character_set_name, default_collation_name 
FROM information_schema.schemata;

2. Use a Specific Database

To start working with a specific database:

USE database_name;

3. Drop a Database

To delete a database:

DROP DATABASE database_name;

⚠️ Warning: Dropping a database deletes all its data permanently.

Privileges Required

To use SHOW DATABASES, the user must have the SHOW DATABASES privilege or some privilege on a database.

Troubleshooting

1. No Databases Listed

• Ensure you are logged in with a user that has sufficient privileges.
• Example:

  mysql -u root -p
  

2. Access Denied Error

• Grant appropriate permissions:

  GRANT SHOW DATABASES ON *.* TO 'username'@'localhost';
  FLUSH PRIVILEGES;
  

Practical Use Case

1. Quickly check if a database exists on the server.
2. Verify the list of databases accessible to the current user.
3. Troubleshoot connectivity or permissions issues by comparing visible databases for different users.

Let me know if you need additional guidance!