Retrieving SQL Server 2005 database information using SMO: properties of database objects
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.
We can use the same structure we defined in the previous section for storing the property values. The main program will be different from the previous program, however. 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 database
Dim svr As Server = New Server(".\sql2k5")
Dim opt As Microsoft.SqlServer.Management.Smo.Property
Dim dtReport As DataTable = util.getStructProperty
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
For Each opt In db.Properties
Dim drReport As DataRow = dtReport.NewRow
drReport("Name") = opt.Name
If Not opt.Value Is Nothing Then
drReport("Value") = opt.Value.ToString
End If
dtReport.Rows.Add(drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
The following statements are a bit different from the previous section.
Dim opt As Microsoft.SqlServer.Management.Smo.Property
The “properties” of “database configuration” return a collection of several “Properties”. Observe carefully the “if” condition, which I mainly used to test for empty objects. This is essential. And the rest is the same.
To work with the above form, we need to fill in the dropdown list with a list of database names using the code below:
And again, “util.getDatabaseList” is defined in “util.vb” as follows:
Public Function getDatabaseList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server) As DataTable
Dim dt As New DataTable
With dt.Columns
.Add("DatabaseName")
End With
For Each db As Microsoft.SqlServer.Management.Smo.Database In svr.Databases
Dim dr As DataRow = dt.NewRow
dr("DatabaseName") = db.Name
dt.Rows.Add(dr)
Next
Return dt
End Function
How to retrieve all properties of a particular SQL Server table using SMO
We can use the same structure we defined in the first section for storing the property values. The main program will be different from the previous program. 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 get all the properties of tables
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
Dim tbl As Table = db.Tables(Me.ComboBox2.Text, Me.ComboBox2.SelectedValue)
Dim opt As Microsoft.SqlServer.Management.Smo.Property
Dim dtReport As DataTable = util.getStructProperty
For Each opt In tbl.Properties
Dim drReport As DataRow = dtReport.NewRow
drReport("Name") = opt.Name
If Not opt.Value Is Nothing Then
drReport("Value") = opt.Value.ToString
End If
dtReport.Rows.Add(drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
To work with the above form, we need to fill in the dropdown list with a list of database names and list of table names (when a particular database is selected). Populating the database names into a dropdown list is explained in the previous section. But populating the table names into the dropdown list would be as follows:
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
And again, “util.getTableList” is defined in “util.vb” as follows:
Public Function getTableList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server, ByRef db As Microsoft.SqlServer.Management.Smo.Database) As DataTable
Dim dt As New DataTable
With dt.Columns
.Add("TableName")
.Add("Schema")
End With
For Each tbl As Microsoft.SqlServer.Management.Smo.Table In db.Tables
We can use the same structure we defined in the first section for storing the property values. The main program will be different from the previous program. 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 get all the properties of views
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
Dim vw As View = db.Views(Me.ComboBox2.Text, Me.ComboBox2.SelectedValue)
Dim opt As Microsoft.SqlServer.Management.Smo.Property
Dim dtReport As DataTable = util.getStructProperty
For Each opt In vw.Properties
Dim drReport As DataRow = dtReport.NewRow
drReport("Name") = opt.Name
If Not opt.Value Is Nothing Then
drReport("Value") = opt.Value.ToString
End If
dtReport.Rows.Add(drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
To work with the above form, we need to fill in the dropdown list with a list of database names and a list of view names (when a particular database is selected). Populating the database names into a dropdown list is explained in the previous section. But populating view names into the dropdown list would be as follows:
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
And again, “util.getViewsList” is defined in “util.vb” as follows:
Public Function getViewsList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server, ByRef db As Microsoft.SqlServer.Management.Smo.Database) As DataTable
Dim dt As New DataTable
With dt.Columns
.Add("ViewName")
.Add("Schema")
End With
For Each vw As Microsoft.SqlServer.Management.Smo.View In db.Views
Dim dr As DataRow = dt.NewRow
dr("ViewName") = vw.Name
dr("Schema") = vw.Schema
dt.Rows.Add(dr)
Next
Return dt
End Function
How to retrieve all properties of a particular SQL Server Stored Procedure using SMO
We can use the same structure we defined in the first section for storing the property values. The main program will be different from the previous program. 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 get all the properties of stored procedures
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
Dim sp As StoredProcedure = db.StoredProcedures(Me.ComboBox2.Text, Me.ComboBox2.SelectedValue)
Dim opt As Microsoft.SqlServer.Management.Smo.Property
Dim dtReport As DataTable = util.getStructProperty
For Each opt In sp.Properties
Dim drReport As DataRow = dtReport.NewRow
drReport("Name") = opt.Name
If Not opt.Value Is Nothing Then
drReport("Value") = opt.Value.ToString
End If
dtReport.Rows.Add(drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
To work with the above form, we need to fill in the dropdown list with a list of database names and a list of stored procedure names (when a particular database is selected). Populating the database names into a dropdown list is explained in an earlier section. But populating stored procedure names into the dropdown list would be as follows:
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
And again, “util.getStoreProcedureList” is defined in “util.vb” as follows:
Public Function getStoredProcedureList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server, ByRef db As Microsoft.SqlServer.Management.Smo.Database) As DataTable
Dim dt As New DataTable
With dt.Columns
.Add("SPName")
.Add("Schema")
End With
For Each sp As Microsoft.SqlServer.Management.Smo.StoredProcedure In db.StoredProcedures
We can use the same structure we defined in the previous section for storing the property values. The main program will be different from the previous program. 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 get all the properties of columns
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
Dim col As Column = db.Tables(Me.ComboBox2.Text).Columns(Me.ComboBox3.Text)
Dim opt As Microsoft.SqlServer.Management.Smo.Property
Dim dtReport As DataTable = util.getStructProperty
For Each opt In col.Properties
Dim drReport As DataRow = dtReport.NewRow
drReport("Name") = opt.Name
If Not opt.Value Is Nothing Then
drReport("Value") = opt.Value.ToString
End If
dtReport.Rows.Add(drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
To work with the above form, we need to fill in the dropdown list with a list of database names, a list of table names (when a particular database is selected) and also a list of column names (when a particular table is selected). Populating the database names and table names is explained in earlier sections of this article. But populating column names in to the dropdown list would be as follows:
Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
In the above code, I don’t have anything important other than “ColumnName” (especially nothing like “Schema” is present). And thus, I assigned “ColumnName” itself as both “DisplayMember” and “ValueMember”.
And again, “util.getColumnsList” is defined in “util.vb” as follows:
Public Function getColumnsList(ByRef svr As Microsoft.SqlServer.Management.Smo.Server, ByRef db As Microsoft.SqlServer.Management.Smo.Database, ByRef tbl As Microsoft.SqlServer.Management.Smo.Table) As DataTable
Dim dt As New DataTable
With dt.Columns
.Add("ColumnName")
End With
For Each col As Microsoft.SqlServer.Management.Smo.Column In tbl.Columns
Dim dr As DataRow = dt.NewRow
dr("ColumnName") = col.Name
dt.Rows.Add(dr)
Next
Return dt
End Function
The entire demonstration solution has been developed using SQL Server 2005 Enterprise Edition and Visual Studio 2005 Professional Edition on Windows Server 2003 Standard Edition. Please note that I didn’t really test the solution on any other versions/editions of the similar suite of Microsoft products.
Any comments, suggestions, ideas, improvements, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
MS SQL Server 
Retrieving SQL Server 2005 Database Info using SMO: Scripting Tables, Views, Sto
