SQL 2005+

Obtaining a List of SQL Server Databases

by Richard Carr, published at http://www.blackwasp.co.uk/SqlServerDatabaseList.aspx

SQL Server includes a number of system stored procedures that allow information to be obtained about the server or instance. One useful stored procedure is sp_Databases, which returns a list of the available databases.

sp_Databases

There are several ways in which you can obtain a list of databases that are available in a SQL Server instance. When you need such a list, you can interrogate SQL Server's system views or you can call the system stored procedure, sp_Databases. If you only want the names or sizes of the databases, the stored procedure is often the simpler option.

EXEC sp_databases

sp_Databases returns a list of databases that has three columns. The first, "DATABASE_NAME", holds the name of the database. The second, "DATABASE_SIZE" gives the size of the database in kilobytes. The third column, which has the name, "REMARKS", always returns null for standard databases.

DATABASE_NAME       DATABASE_SIZE  REMARKS  
------------------------------------------
master              6784           NULL
model               11776          NULL
msdb                45248          NULL
ReportServer        13696          NULL
ReportServerTempDB  5200           NULL
tempdb              8704           NULL
Test                12672          NULL

The results above show the output of sp_Databases for a SQL Server instance that has Reporting Services installed and has one user-created database. You can see that the result set includes all items, including system databases.

17 August 2015