Executing multiple SQL statements against multiple databases

In my day job, each of the customers running in our hosted environment have their own SQL Server database. As we develop the software that runs on top of these databases, we often need to update the schema of each database. I knocked together a little console application in C# to do the job and here are the interesting parts of it. First, we need to get a list of the available databases, which can be achieved quite easily.

      List<string> DBs = new List<string>();
      // get list of available databases
      SqlCommand command = conn.CreateCommand();
      command.CommandText = "select * from master.sys.databases where DataBase_ID > 4";
      using (SqlDataReader reader = command.ExecuteReader())
      {
        while (reader.Read())
        {
          DBs.Add(reader.GetString(0));
        }
      }
      DBs.Sort();

Next, we need to execute the SQL updates. This isn’t quite as easy as you’d think. If you want to execute multiple SQL statements with GO statements between them, SqlCommand.ExecuteNonQuery won’t handle them. Fortunately SQL Server comes with some assemblies that solve the problem. So you need to add references to Microsoft.SqlServer.ConnectionInfo.dll, Microsoft.SqlServer.Management.Sdk.Sfc.dll and Microsoft.SqlServer.Smo.dll. Once they have been referenced, the following code should be able to handle any SQL you throw at it.

      foreach (string database in DBs)
      {
        Microsoft.SqlServer.Management.Smo.Server server = 
          new Microsoft.SqlServer.Management.Smo.Server(new ServerConnection(conn));
        server.ConnectionContext.ExecuteNonQuery("USE " + database + "\nGO\n" + sql);
      }