Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

June 3, 2010

Build a List of SQL Server Database Names

Filed under: C#,VB.NET @ 4:17 pm

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:

image

Use this technique any time you need to obtain the list of database names from SQL Server.

Enjoy!

6 Comments

  1.   Irasimus — June 4, 2010 @ 2:26 pm    Reply

    FYI, the same information can be obtained by calling the SqlClient.SqlConnection.GetSchema(“Databases”) method, which returns a datatable containing the database names.

  2.   Zielyn — June 4, 2010 @ 6:03 pm    Reply

    ‘select name from sys.databases’ works better since it requires less permissions. You can also get a lot more information from that view instead of running sp_databases.

  3.   DeborahK — June 4, 2010 @ 6:53 pm    Reply

    Thanks, Zielyn and Irasimus for the alternatives!

  4.   Philip of Platypus Software Pty Ltd — November 24, 2010 @ 6:15 am    Reply

    Hi Deborah,

    Thanks so much for taking the time out to help other developers. Very much appreciated :)

  5.   Brian Barnett — May 24, 2011 @ 8:55 am    Reply

    Yet another method is by using SMO.

    Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(conn));
    foreach (Microsoft.SqlServer.Management.Smo.Database db in server.Databases)
    {
    databases.Add(db.Name);
    }

  6.   Ahmed — April 6, 2013 @ 7:23 am    Reply

    Thank you Sir

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2014 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs