Retrieving SQL Server 2005 database information using SMO: properties of database objects
(Page 1 of 4 )
This article is the last in a series focusing on retrieving SQL Server 2005 information using SMO together with Visual Basic 2005 and Visual Studio 2005. Some of the topics we will cover in this article include retrieving all properties of an SQL Server Instance, an SQL Server database, an SQL Server table, and so on.
A downloadable file for this article is available
here.
Since I already explained SMO, DMO and so on in the first article of this series, I shall straight away drive into the technical issues of using SMO. I also covered the concept of “working with SMO using Visual Studio 2005” in my first article. If you are new to SMO (or having any trouble working with a new Visual Studio 2005 solution), I strongly suggest you go through that article.
How to retrieve all properties of a particular SQL Server instance using SMO
Before proceeding to solve this issue, we need to define a structure which can hold all the properties along with their values. The structure has been created under “util.vb”. The code for the structure is as follows:
Public Function getStructProperty() As DataTable
Dim dt As New DataTable
dt.Columns.Add("Name")
dt.Columns.Add("value")
Return dt
End Function
Now that the structure to hold properties is ready, we can start by retrieving all the properties. Let us proceed with the following code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'to retrieve all properties of SQL Server instance
Dim svr As Server = New Server (".\sql2k5")
Dim opt As ConfigProperty
Dim dtReport As DataTable = util.getStructProperty
For Each opt In svr.Configuration.Properties
Dim drReport As DataRow = dtReport.NewRow
drReport ("Name") = opt.DisplayName
drReport ("Value") = opt.ConfigValue
dtReport.Rows.Add (drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
The most important statements from the above code fragment are the following ones:
Imports Microsoft.SqlServer.Management.Smo
The above statement imports the necessary SMO namespace (which maintains the central SMO hierarchy).
Dim svr As Server = New Server(".\sql2k5")
The above statement connects to the local named instance (“sql2k5”).
Dim dtReport As DataTable = util.getStructProperty
The above statement works with the previous routine (which we created in the beginning).
Dim opt As ConfigProperty
For Each opt In svr.Configuration.Propertie
The first statement is used to go through all the properties available. The “properties” of “server configuration” returns a collection of several “ConfigProperties”. And the rest is the same.
Next: How to retrieve all properties of a particular SQL Server database using SMO >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 8 
