Deborah's Developer MindScape

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.

In C#:

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";

    if (!string.IsNullOrWhiteSpace(connString))
    {
        using (SqlConnection cn = new SqlConnection(connString))
        {
            // Open the connection
            cn.Open();

            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_databases";

                using (SqlDataReader myReader = cmd.ExecuteReader())
                {
                    while ((myReader.Read()))
                    {
                        databaseNames.Add(myReader.GetString(0));
                    }
                }
            }
        }
    } 
    return databaseNames;
}

In VB:

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
            cn.Open()

            Using cmd As SqlCommand = New SqlCommand()
                cmd.Connection = cn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "sp_databases"

                Using myReader As SqlDataReader = cmd.ExecuteReader()
                    While (myReader.Read())
                        databaseNames.Add(myReader.GetString(0))
                    End While
                End Using
            End Using
        End Using
    End If

    Return databaseNames
End Function

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.

In C#:

DatabaseComboBox.DataSource = databaseNames;

In VB:

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.

Enjoy!