Tip of the Day : SQL Server Database Design - Twitter Tweets

SQL Server Helper - Tip of the Day

How to Get a List of Databases Within a SQL Server Instance

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