Retrieving SQL Server 2005 Database Info Using SMO: Database Info, Table Info - How do we retrieve basic info about all databases from a SQL Server instance using SMO?
(Page 2 of 5 )
To work with the data table in the previous section, I created a separate “form” with the following code:
Imports Microsoft.SqlServer.Management.Smo
Public Class Form3
Private Sub Button1_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles Button1.Click
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database
Dim dtReport As DataTable = util.getStructDatabaseBasic
For Each db In svr.Databases
Dim drReport As DataRow = dtReport.NewRow
drReport ("Name") = db.Name
drReport ("Tables") = db.Tables.Count
drReport ("Views") = db.Views.Count
drReport ("StoredProcedures") = db.StoredProcedures.Count
drReport ("UserDefinedDataTypes") =
db.UserDefinedDataTypes.Count
drReport ("UserDefinedAggregates") =
db.UserDefinedAggregates.Count
drReport ("UserDefinedFunctions") =
db.UserDefinedFunctions.Count
drReport ("UserDefinedTypes") = db.UserDefinedTypes.Count
drReport ("Synonyms") = db.Synonyms.Count
drReport ("Triggers") = db.Triggers.Count
dtReport.Rows.Add(drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
End Class
The most important statements from the above code fragment are the following statements:
Imports Microsoft.SqlServer.Management.Smo
….
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database
…
For Each db In svr.Databases
The first statement imports the necessary SMO namespace (which maintains the central SMO hierarchy). The rest of the statements mainly connect to a named instance available within the local computer, and retrieve all database-related information into “db” for each iteration of the “FOR” loop.
The collection “databases” holds all information about the databases existing within an SQL Server instance. This needs to be invoked using a server object (in this case, it is “svr”).
Next: How do we retrieve advanced info on all databases from a SQL Server instance using SMO? >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 12 
