Allen White

Execute SQL Scripts against multiple DB's with SMO

In a previous post (Using SMO to set Database Properties) I covered building a structure for a utility program using SMO. Today I needed to run a SQL script sent by one of the developers at my company against over 100 databases with similar structures.

First, I pull the script into a string field in one quick read operation:

        Dim allText As String

        Using FileReader As New _
        Microsoft.VisualBasic.FileIO.TextFieldParser("C:\script.sql")
            allText = FileReader.ReadToEnd
        End Using

We support two different company's databases with this application, and separate the databases via a three letter code at the beginning of each database name. Other databases on that server don't follow that convention, so it's easy to use those codes to select just the databases I want the changes made to. The core code is as follows:

        ' Browse the database collection on the target server
        ' Browse the database collection on the target server
        Dim lisDBs As DatabaseCollection
        lisDBs = srvMgmtServer.Databases
        Dim objDB As Database
        For Each objDB In lisDBs
            If Left(objDB.Name, 4) = "xxx_" Or Left(objDB.Name, 4) = "yyy_" Then
                objDB.ExecuteNonQuery(allText)
            End If
        Next

This sure beats switching from one database to the next in Management Studio and executing the script over 100 times!

Allen