In SQL Server you can use the sp_catalogs system stored procedure to return a full list of databases in a linked server.

Strictly speaking, this stored procedure returns the list of catalogs in the specified linked server. This is equivalent to databases in SQL Server.

Syntax

The syntax goes like this:

sp_catalogs [ @server_name = ] 'linked_svr'

The @server_name argument is the name of the linked server that you want the list of databases from.

Example

Here’s an example of returning a list of databases from a linked server called Homer.

EXEC sp_catalogs @server_name = 'Homer';

It can also be run like this:

EXEC sp_catalogs 'Homer';

Result:

+----------------------+---------------+
| CATALOG_NAME         | DESCRIPTION   |
|----------------------+---------------|
| BandTest             | NULL          |
| CompareTypes         | NULL          |
| EMS                  | NULL          |
| ExamineDateTime2     | NULL          |
| FK_Test              | NULL          |
| master               | NULL          |
| model                | NULL          |
| msdb                 | NULL          |
| Music                | NULL          |
| PK_Test              | NULL          |
| tempdb               | NULL          |
| Test                 | NULL          |
| Test2                | NULL          |
| WideWorldImportersDW | NULL          |
+----------------------+---------------+

In this case, there are 14 databases in the linked server.

The stored procedure returns the catalog/database name and its description, if there is one. In this case, no description is available for any of the databases.