Retrieving a list of all database names in an SQL Server instance is a common task for database administrators and developers. To retrieve a list of all database names in SQL Server, you can use SQL queries or system views. Here are a few methods to achieve this:

Using the sys.databases System View

SQL Server provides a system view called sys.databases, which contains information about all the databases on the server. You can query this view to get a list of database names.

SELECT name
FROM sys.databases
WHERE database_id > 4;

This query retrieves the names of all user-created databases by filtering out the system databases with database_id less than or equal to 4.

Using the sp_databases Stored Procedure

Another way to retrieve database names is by using the sp_databases stored procedure. This stored procedure provides a list of all databases on the SQL Server instance.

EXEC sp_databases;

This will display a result set with information about all databases on the server, including the database names.

Using the INFORMATION_SCHEMA.CATALOGS View

If you want to retrieve a list of database names in a more ANSI SQL-compliant way, you can use the INFORMATION_SCHEMA.CATALOGS view.

SELECT catalog_name
FROM INFORMATION_SCHEMA.CATALOGS;

This query will provide you with the database names using ANSI SQL standards.

Choose the method that best suits your needs and access privileges. The first two methods are commonly used in SQL Server, while the third method provides a more standardized approach for cross-DBMS compatibility.