SQL - Show Databases: A Beginner's Guide

Hello, aspiring database enthusiasts! Today, we're going to embark on an exciting journey into the world of SQL, specifically focusing on how to show or list databases. Don't worry if you've never written a line of code before – I'll be your friendly guide, and we'll take this step by step. So, grab a cup of coffee, and let's dive in!

What are Databases?

Before we jump into showing databases, let's quickly understand what a database is. Imagine a digital filing cabinet where you can store, organize, and retrieve information. That's essentially what a database is! In the world of computers, we use databases to keep track of all sorts of data, from your favorite online shopping site's inventory to your social media posts.

Why Do We Need to Show Databases?

Now, you might be wondering, "Why do we need to show databases?" Well, imagine you're a librarian in charge of multiple libraries across a city. Wouldn't it be helpful to have a list of all the libraries you're managing? That's exactly what showing databases does for us in SQL – it gives us an overview of all the databases we have access to.

List Databases using SQL

In SQL, there are several ways to list or show databases. Let's explore each method one by one.

The SHOW DATABASES Statement

The most straightforward way to list all databases is by using the SHOW DATABASES statement. It's like asking SQL, "Hey, can you show me all the databases we have?"

SHOW DATABASES;

When you run this command, SQL will return a list of all the databases you have access to. It might look something like this:

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

Each row in this result represents a database. The ones like 'information_schema', 'mysql', 'performance_schema', and 'sys' are system databases that come with MySQL. Any databases you've created yourself (like 'my_first_database' in this example) will also appear in this list.

The SHOW SCHEMAS Statement

Did you know that 'schema' is another word for 'database' in SQL? It's true! Because of this, we can also use the SHOW SCHEMAS statement, which does exactly the same thing as SHOW DATABASES.

SHOW SCHEMAS;

This will give you the same result as SHOW DATABASES. It's like asking for a menu in a restaurant – whether you say "Can I see the menu?" or "Can I see the list of dishes?", you'll get the same thing!

The SELECT...FROM Statement

For those who like to flex their SQL muscles a bit more, there's another way to list databases using a SELECT statement. This method involves querying the INFORMATION_SCHEMA, which is a database that stores information about all the other databases.

SELECT SCHEMA_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA;

This query says, "Please select the names of all schemas (databases) from the INFORMATION_SCHEMA's SCHEMATA table." The result will look similar to what we got with SHOW DATABASES, but in a slightly different format:

+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| my_first_database  |
+--------------------+

The EXEC sp_databases Statement (SQL Server)

If you're using Microsoft SQL Server instead of MySQL, you have another option: the EXEC sp_databases statement. This is like asking a helpful robot to run a pre-written program that fetches the list of databases for you.

EXEC sp_databases;

This will return a result set with additional information about each database:

+--------------------+--------------+-------------------+
| DATABASE_NAME      | DATABASE_SIZE| REMARKS           |
+--------------------+--------------+-------------------+
| master             | 6392         | NULL              |
| tempdb             | 16384        | NULL              |
| model              | 16384        | NULL              |
| msdb               | 15928        | NULL              |
| my_first_database  | 8192         | NULL              |
+--------------------+--------------+-------------------+

Here, you not only see the database names but also their sizes and any remarks (which are usually NULL unless you've added custom remarks).

Comparison of Methods

Let's summarize these methods in a handy table:

Method	Syntax	Database System	Ease of Use	Additional Info
SHOW DATABASES	SHOW DATABASES;	MySQL, MariaDB	Very Easy	None
SHOW SCHEMAS	SHOW SCHEMAS;	MySQL, MariaDB	Very Easy	None
SELECT...FROM	SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;	MySQL, MariaDB, PostgreSQL	Moderate	Customizable
EXEC sp_databases	EXEC sp_databases;	SQL Server	Easy	Shows size and remarks

Conclusion

And there you have it, folks! We've explored four different ways to show or list databases in SQL. Whether you prefer the simplicity of SHOW DATABASES, the flexibility of a SELECT statement, or the extra information from EXEC sp_databases, you now have the tools to get a bird's-eye view of your database landscape.

Remember, being able to list your databases is like having a map of your data world. It's the first step in navigating the vast seas of information at your fingertips. As you continue your SQL journey, you'll find yourself using these commands often, especially when you're working with new systems or trying to get your bearings in a complex database environment.

Keep practicing, stay curious, and don't be afraid to experiment. Before you know it, you'll be querying databases like a pro! Until next time, happy coding!

Credits: Image by storyset