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.
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