Retrieving SQL Server 2005 Database Info Using SMO: Database Info, Table Info - How do we retrieve advanced info on all databases from a SQL Server instance using SMO?
(Page 3 of 5 )
Previous sections discussed dealing with only basic information (generally needed by programmers). Now, in this section, I shall further extend what we are doing with some “advanced” information (generally needed by DBAs) related to every database.
Before proceeding further, we again needed to define a new structure to hold the advanced database information. Let us look into that.
Public Function getStructDatabaseAdvanced() As DataTable
Dim dt As New DataTable
With dt.Columns
.Add (New DataColumn ("Name"))
.Add (New DataColumn ("Status"))
.Add (New DataColumn ("PrimaryFilePath"))
.Add (New DataColumn ("Size"))
.Add (New DataColumn ("SpaceAvailable"))
.Add (New DataColumn ("DataSpaceUsage"))
.Add (New DataColumn ("ActiveConnections"))
.Add (New DataColumn ("Assemblies"))
.Add (New DataColumn ("DatabaseGuid"))
.Add (New DataColumn ("ExtendedStoredProcedures"))
.Add (New DataColumn ("FileGroups"))
.Add (New DataColumn ("FullTextCatalogs"))
.Add (New DataColumn ("LogFiles"))
.Add (New DataColumn ("LastBackupDate"))
End With
Return dt
End Function
This is very similar to the one I defined in the first section. Now, let us proceed with the code to retrieve the information.
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database
Dim dtReport As DataTable = util.getStructDatabaseAdvanced
For Each db In svr.Databases
Dim drReport As DataRow = dtReport.NewRow
drReport ("Name") = db.Name
drReport ("Status") = db.Status
drReport ("PrimaryFilePath") = db.PrimaryFilePath
drReport ("Size") = db.Size
drReport ("SpaceAvailable") = db.SpaceAvailable
drReport ("DataSpaceUsage") = db.DataSpaceUsage
drReport ("ActiveConnections") = db.ActiveConnections
drReport ("Assemblies") = db.Assemblies.Count
drReport ("DatabaseGuid") = db.DatabaseGuid
drReport ("ExtendedStoredProcedures") =
db.ExtendedStoredProcedures.Count
drReport ("FileGroups") = db.FileGroups.Count
drReport ("FullTextCatalogs") = db.FullTextCatalogs.Count
drReport ("LogFiles") = db.LogFiles.Count
drReport ("LastBackupDate") = db.LastBackupDate
dtReport.Rows.Add(drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
Next: How do we retrieve security info about all databases from a SQL Server instance using SMO? >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 11 
