This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Obtaining a List of SQL Server Databases

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.


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.

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