Previous
Written by: rJTutorial Team | Last Updated:

SQL SHOW DATABASES

The SHOW DATABASES statement is a fundamental command used to list all the databases currently available on a database server. Think of it as opening a "File Explorer" for your SQL server it allows you to see exactly which projects and data silos you have permission to access before you start writing queries.

Developer Tip: This is usually the very first command you run when connecting to a new or unfamiliar server to get your bearings and understand the environment.

 

Key Features of SQL SHOW DATABASES

  • System Overview: It provides an immediate snapshot of all logical database containers on the server.
  • Security-Aware: The list you see is filtered based on your user privileges. If your account doesn't have permissions for a specific database, it won't appear in the list.
  • Platform Specific: While it is the standard command for MySQL and MariaDB, other systems like PostgreSQL or SQL Server use different methods to achieve the same result.
Watch Out: Running this command on a production server with thousands of databases can sometimes cause a slight delay. Always ensure you have the necessary administrative rights to view the full list.

 

SQL SHOW DATABASES Syntax

The syntax is incredibly straightforward. You don't need to specify any tables or columns because you are querying the server's metadata itself.

SHOW DATABASES;

Output Example:

Database Names

Database Name
information_schema
mysql
performance_schema
test_db

When you run this, you will notice system databases like information_schema and mysql. These are internal databases used by the engine to store metadata and configurations. Your actual project data will typically be in a custom-named database like test_db.

Common Mistake: Beginners often try to run SELECT * FROM DATABASES;. In MySQL, this will result in an error. Always use the SHOW keyword for this specific task.

 

Using LIKE to Filter Databases

In environments with dozens or hundreds of databases, scrolling through a long list is inefficient. You can use the LIKE clause with wildcards to find exactly what you're looking for.

The percent sign (%) acts as a wildcard representing zero or more characters.

-- Find databases that start with "test"
SHOW DATABASES LIKE 'test%';

Output:

Database Name
test_db
test_analytics
Best Practice: Use consistent naming conventions (e.g., prod_users, dev_users, test_users). This makes filtering with LIKE much more powerful when managing multiple environments.

 

Checking Databases in SQL Server

It is important to remember that SQL syntax can vary between different Database Management Systems (DBMS). If you are working in Microsoft SQL Server, the SHOW DATABASES command will not work. Instead, you query the system catalog:

-- SQL Server variant
SELECT name FROM sys.databases;

Similarly, in PostgreSQL, you would use the command line shortcut \l or query the pg_database table.

 

Summary

  • MySQL/MariaDB: Use SHOW DATABASES; to see all available schemas.
  • Filtering: Use LIKE 'pattern%' to narrow down the results when dealing with many databases.
  • Cross-Platform: Always check your specific DBMS documentation, as SQL Server and PostgreSQL use different system queries (like sys.databases).
  • Context: After seeing the list, your next step is usually the USE database_name; command to start working within one.
Previous