MySQL Show Databases

Last updated: October 19, 2026Oct 19, 2026 4 min read

When you connect to a MySQL server, the first thing you often want to know is which databases are here? The SHOW DATABASES statement lists them. This tutorial covers the statement, how to filter the list, how to get the same information (and more, such as sizes) from information_schema, and what privileges affect the result.

1. Listing All Databases

Our sample server has four user databases. List everything:

SHOW DATABASES;

Result: 8 rows

2. Filtering with LIKE

Add a LIKE pattern to show only matching names. % matches any number of characters and _ matches exactly one:

SHOW DATABASES LIKE 'sales%';

Result: 2 rows

SHOW DATABASES LIKE '%\_portal';   -- \_ means a literal underscore

Result: 1 row

3. Filtering with WHERE

The result has one column, named Database. You can filter on it with WHERE, which allows any condition:

SHOW DATABASES
WHERE `Database` NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

Result: 4 rows

4. Using information_schema.schemata

SHOW DATABASES is convenient, but a normal SELECT on information_schema.schemata gives more columns and can be sorted, joined and used in programs:

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY schema_name;

Result: 4 rows

5. How Many Tables Are in Each Database?

SELECT s.schema_name AS db, COUNT(t.table_name) AS tables
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON t.table_schema = s.schema_name
WHERE s.schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY s.schema_name
ORDER BY tables DESC, db;

Result: 4 rows

The LEFT JOIN keeps empty databases such as blog, showing 0 tables.

6. Finding the Size of Each Database

information_schema.tables holds approximate data and index sizes per table. Add them up per database:

ANALYZE TABLE sales_2026.orders, sales_2026.customers, hr_portal.staff;
SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024, 1) AS size_kb
FROM information_schema.tables
WHERE table_schema IN ('sales_2026', 'hr_portal')
GROUP BY table_schema;

Result: 2 rows

7. From the Command Line

You can list databases without opening the interactive client:

mysql -u root -p -e "SHOW DATABASES;"
mysqlshow -u root -p              # lists databases
mysqlshow -u root -p sales_2026   # lists the tables in one database

8. Why Some Users See Fewer Databases

SHOW DATABASES only lists databases on which you have some privilege, unless you have the global SHOW DATABASES privilege. Let's create a user who can only read blog:

CREATE USER 'writer'@'localhost' IDENTIFIED BY 'Wr1ter#2026';
GRANT SELECT ON blog.* TO 'writer'@'localhost';
SHOW GRANTS FOR 'writer'@'localhost';

Result: 2 rows

Now log in as writer and run the same statement:

SHOW DATABASES;

Result: 3 rows

writer sees blog plus the two metadata databases every account can see, information_schema and performance_schema. The application databases hr_portal, sales_2025 and sales_2026 are hidden, which protects other teams' database names from view.

9. Listing Databases from Java

import java.sql.*;

public class ListDatabases {
    public static void main(String[] args) throws SQLException {
        try (Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "your-password");
             ResultSet rs = con.getMetaData().getCatalogs()) {      // MySQL databases are JDBC "catalogs"
            while (rs.next()) {
                System.out.println(rs.getString("TABLE_CAT"));
            }
        }
    }
}

10. Practice Exercises

SHOW DATABASES LIKE '%20__';

SELECT s.schema_name
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON t.table_schema = s.schema_name
WHERE s.schema_name NOT IN ('mysql','information_schema','performance_schema','sys')
GROUP BY s.schema_name
HAVING COUNT(t.table_name) = 0;

11. Summary

• SHOW DATABASES (or SHOW SCHEMAS) lists the databases you are allowed to see.
• Filter with LIKE 'pattern' or WHERE `Database` ....
• information_schema.schemata gives the same list as a normal table, with character sets and collations.
• Join with information_schema.tables for table counts and sizes.
• Users without the global SHOW DATABASES privilege see only databases they have privileges on.