In order to loop through (iterate through) the list of available database in a network using SQL SMO:
Set references to:
Microsoft.SplServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum
I've included all the SMO routines that I have below
Imports Microsoft.SqlServer.Management.Smo
Imports System.Data
Public Class facSMO
Function getServers() As DataTable
Try
Return SmoApplication.EnumAvailableSqlServers(False)
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Function getServer(ByVal strServer As String) As Microsoft.SqlServer.Management.Smo.Server
Try
Dim svr As Server = New Server(strServer)
Return svr
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Function getDatabases(ByVal strServer As String) As DataTable
Dim b As Boolean
b = False
Try
Dim svr As Server = New Server(strServer)
Dim dt As New DataTable
With dt.Columns
.Add("Name")
.Add("selected")
dt.Columns("selected").DataType = System.Type.GetType("System.Boolean")
End With
For Each db As Microsoft.SqlServer.Management.Smo.Database In svr.Databases()
Dim dr As DataRow = dt.NewRow
dr("Name") = db.Name
dr("Selected") = 0
dt.Rows.Add(dr)
Next
Return dt
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Function
Sub runQuery(ByVal strServer As String, ByVal strDB As String, ByVal strText As String)
Try
'objDB.ExecuteNonQuery(allText)
'Dim db As Microsoft.SqlServer.Management.Smo.Database()
Dim SMOServer As Server = New Server(strServer)
Dim db As Database = SMOServer.Databases(strDB)
db.ExecuteNonQuery(strText)
Catch ex As Exception
Throw New Exception(ex.Message)
End Try
End Sub
End Class