Below are three ways we can use T-SQL to return a list of databases in SQL Server.

The sp_databases Stored Procedure

In SQL Server, the sp_databases stored procedure lists the databases that either reside in an instance of the SQL Server or are accessible through a database gateway.

Here’s an example of executing this 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      |
| tempdb                | 24576           | NULL      |
| WideWorldImporters    | 3575808         | NULL      |
| World                 | 81920           | NULL      |
+-----------------------+-----------------+-----------+

If the statement is not the first one in a batch, you’ll need to prefix the procedure name with either EXEC or EXECUTE.

So the following three commands are equivalent:

sp_databases;
EXEC sp_databases;
EXECUTE sp_databases;

But the first one can only be used if it’s the first statement in a batch.

The sys.databases View

The sys.databases view contains one row per database in the instance of SQL Server.

Here’s an example of querying this view:

SELECT name  
FROM sys.databases;

Example result:

+-----------------------+
| name                  |
|-----------------------|
| master                |
| tempdb                |
| model                 |
| msdb                  |
| Music                 |
| KrankyKranes          |
| WideWorldImporters    |
| World                 |
| NarrowNationExporters |
+-----------------------+

This view contains a lot of columns, and you can join it with other views/tables, so it’s an ideal option for when you need more information than the sp_databases procedure returns.

The sys.sysdatabases Table/View

The sys.sysdatabases is the equivalent to sys.databases.

So we could simply swap sys.databases in the above example to sys.sysdatabases to get the same result:

SELECT name  
FROM sys.sysdatabases;

Result:

+-----------------------+
| name                  |
|-----------------------|
| master                |
| tempdb                |
| model                 |
| msdb                  |
| Music                 |
| KrankyKranes          |
| WideWorldImporters    |
| World                 |
| NarrowNationExporters |
+-----------------------+

However, you should avoid this option.

This SQL Server 2000 system table is included in current SQL Server releases as a view for backward compatibility. It will be removed in a future version of Microsoft SQL Server. Microsoft recommends that we avoid using this feature in new development work, and plan to modify applications that currently use this feature.

So if you encounter an old script that references sys.sysdatabases, you should think about changing that to sys.databases.

Linked Servers

If you need to get a list of databases from a linked server, use sp_catalogs while passing in the name of the linked server.

See List All Databases from a Linked Server in SQL Server for more information and examples.