Build a List of SQL Server Database Names
Thu, Jun 3 2010 14:17
The trick to obtaining the list of database names from SQL Server is to know the name of the system stored procedure that you need to call.
This post presents a static/shared method for obtaining the names of the databases in a particular SQL Server instance.
public static List<string> GetDatabaseNames()
string connString = null;
List<string> databaseNames = new List<string>();
// Be sure to replace this with your connection string.
connString = "Data Source=.\sqlexpress;Integrated Security=True";
using (SqlConnection cn = new SqlConnection(connString))
// Open the connection
using (SqlCommand cmd = new SqlCommand())
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_databases";
using (SqlDataReader myReader = cmd.ExecuteReader())
Public Shared Function GetDatabaseNames() As List(Of String)
Dim connString As String
Dim databaseNames As New List(Of String)
' Be sure to replace this with your connection string.
connString = "Data Source=.\sqlexpress;Integrated Security=True"
If Not String.IsNullOrWhiteSpace(connString) Then
Using cn As SqlConnection = New SqlConnection(connString)
' Open the connection
Using cmd As SqlCommand = New SqlCommand()
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp_databases"
Using myReader As SqlDataReader = cmd.ExecuteReader()
This code begins by defining a new generic List of strings. This list defines the return value for the function.
The connection string is hard-coded to an instance of SQL Server express with Windows authentication. To make this code more generalized, you could pass in the connection string or read it from a configuration file. It was hard-coded here just to keep the example simple.
The routine then defines and opens the connection and defines a command. The CommandType is stored procedure and the stored procedure name is "sp_databases". This is a system stored procedure and should exist in your SQL Server.
The routine executes the command returning a DataReader with the results of the stored procedure. Each database name is obtained using the DataReader and added to the list.
Once you have the list of names in a generic List, you can do just about anything with them. For example, you can bind them to a ComboBox.
DatabaseComboBox.DataSource = databaseNames;
DatabaseComboBox.DataSource = databaseNames
The result is shown below:
Use this technique any time you need to obtain the list of database names from SQL Server.