There are several ways to show a list of databases in SQL Server. Probably the easiest way is to use the sp_databases stored procedure.

The sp_databases stored procedure is a kind of SQL Server equivalent to the SHOW DATABASES command that can be used with DBMSs such as MySQL and MariaDB. OK, maybe they aren’t strictly equivalents, but based on their ease of use, they could be viewed as such from a user’s perspective.

Example

Here’s an example of how to execute this stored procedure:

sp_databases;

Example result:

+-----------------------+-----------------+-----------+
| DATABASE_NAME         | DATABASE_SIZE   | REMARKS   |
|-----------------------+-----------------+-----------|
| KrankyKranes          | 16384           | NULL      |
| master                | 6848            | NULL      |
| model                 | 16384           | NULL      |
| msdb                  | 79040           | NULL      |
| Music                 | 16384           | NULL      |
| NarrowNationExporters | 147456          | NULL      |
| PetHotel              | 16384           | NULL      |
| StereoSystems         | 16384           | NULL      |
| tempdb                | 24576           | NULL      |
| Test                  | 107520          | NULL      |
| WideWorldImporters    | 3575808         | NULL      |
| World                 | 81920           | NULL      |
+-----------------------+-----------------+-----------+

This procedure also returns the database size, which can be quite handy (although this column returns a NULL value for databases larger than 2.15 TB).

Another way to execute the procedure is by prefixing it with EXEC or EXECUTE.

Therefore, all the following are equivalent:

sp_databases;
EXEC sp_databases;
EXECUTE sp_databases;

You can only omit EXEC/EXECUTE when the statement is the first one in a batch.