Skip Navigation Links
SQL Server 2012
SQL Server 2014
SQL Server 2016
Practice Test
Tip of the Day : Example Uses of the SYSDATETIME Date Function
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

There are different ways of listing all databases within an instance of SQL Server.  The first method is with the use of the sp_databases system stored procedure:

EXEC sp_databases 

The sp_databases lists databases that either reside in an instance of the SQL Server Database Engine or are accessible through a database gateway.

Another way of getting a list of all databases within an instance of SQL Server is with the sp_helpdb system stored procedure.  The sp_helpdb system stored procedure reports information about a specified database or all databases.  If no database name is passed to the sp_helpdb system stored procedure, it will display information about all databases on the server running SQL Server.

EXEC sp_helpdb

Yet another way of getting a list of databases within an instance of SQL Server is by querying the sys.databases system view.

FROM [sys].[databases]

Regardless of which method to use, all of these methods will return not just user databases but system databases as well, such as the master, model, msdb and tempdb databases.  If the SQL Server instance has Reporting Services installed, the ReportServer and ReportServerTempDB databases will also be included in the list.

Back to Tip of the Day List Next Tip