If you’re using SQL Server via a command line interface, you probably don’t see a persistent list of databases like you normally would while using a GUI (such as SSMS or Azure Data Studio).

In such cases, you can use the sp_databases system stored procedure to return a list of databases. This stored procedure specifically lists databases that either reside in an instance of the SQL Server or are accessible through a database gateway.

This is probably the quickest and easiest way of listing all databases using T-SQL. You can simply type sp_databases for a list of databases, or to be more explicit, EXEC sp_databases.

Syntax

The syntax goes like this:

sp_databases

That’s all there is. No arguments are required or accepted.

Example

Here’s an example of usage.

EXEC sp_databases;

Here’s the result I get in one of my test environments:

+--------------------+-----------------+-----------+
| DATABASE_NAME      | DATABASE_SIZE   | REMARKS   |
|--------------------+-----------------+-----------|
| master             | 6592            | NULL      |
| model              | 16384           | NULL      |
| msdb               | 15872           | NULL      |
| Music              | 16384           | NULL      |
| MyDB               | 16384           | NULL      |
| tempdb             | 24576           | NULL      |
| Test               | 16384           | NULL      |
| WideWorldImporters | 3248128         | NULL      |
| world              | 16384           | NULL      |
+--------------------+-----------------+-----------+

And if that’s too much to type, here it is without the EXEC:

sp_databases

That returns the same results.

Databases on a Linked Server

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.