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.

{mospagebreak title=How to retrieve all properties of a particular SQL Server database using SMO}

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:

Dim svr As Server = New Server(“.sql2k5″)
        Me.ComboBox1.DataSource = util.getDatabaseList(svr)
        Me.ComboBox1.DisplayMember = “DatabaseName”

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
        Try
            Dim svr As Server = New Server(“.sql2k5″)
            Me.ComboBox2.DataSource = util.getTableList(svr, svr.Databases(Me.ComboBox1.Text))
            Me.ComboBox2.DisplayMember = “TableName”
            Me.ComboBox2.ValueMember = “Schema”
        Catch ex As Exception
            Me.ComboBox2.DataSource = Nothing
            Me.ComboBox2.Items.Clear()
        End Try
    End Sub

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
            Dim dr As DataRow = dt.NewRow
            dr(“TableName”) = tbl.Name
            dr(“Schema”) = tbl.Schema
            dt.Rows.Add(dr)
        Next
 
        Return dt
    End Function

{mospagebreak title=How to retrieve all properties of a particular SQL Server View 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 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
        Try
            Dim svr As Server = New Server(“.sql2k5″)
            Me.ComboBox2.DataSource = util.getViewsList(svr, svr.Databases(Me.ComboBox1.Text))
            Me.ComboBox2.DisplayMember = “ViewName”
            Me.ComboBox2.ValueMember = “Schema”
        Catch ex As Exception
            Me.ComboBox2.DataSource = Nothing
            Me.ComboBox2.Items.Clear()
        End Try
    End Sub

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
        Try
            Dim svr As Server = New Server(“.sql2k5″)
            Me.ComboBox2.DataSource = util.getStoredProcedureList(svr, svr.Databases(Me.ComboBox1.Text))
            Me.ComboBox2.DisplayMember = “SPName”
            Me.ComboBox2.ValueMember = “Schema”
        Catch ex As Exception
            Me.ComboBox2.DataSource = Nothing
            Me.ComboBox2.Items.Clear()
        End Try
    End Sub

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
            Dim dr As DataRow = dt.NewRow
            dr(“SPName”) = sp.Name
            dr(“Schema”) = sp.Schema
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

{mospagebreak title=How to retrieve all properties of a particular SQL Server column using SMO}

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
        Try
            Dim svr As Server = New Server(“.sql2k5″)
            Me.ComboBox3.DataSource = util.getColumnsList(svr, svr.Databases(Me.ComboBox1.Text), svr.Databases(Me.ComboBox1.Text).Tables(Me.ComboBox2.Text))
            Me.ComboBox3.DisplayMember = “ColumnName”
            Me.ComboBox3.ValueMember = “ColumnName”
        Catch ex As Exception
            Me.ComboBox3.DataSource = Nothing
            Me.ComboBox3.Items.Clear()
        End Try
    End Sub

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.

2 thoughts on “Retrieving SQL Server 2005 database information using SMO: properties of database objects

  1. Hello guys, another beautiful contribution of SQL Server 2005 SMO. Enjoy. Any new ideas (or articles) needed, do not hesitate to post them. bye

  2. was looking for this information all day.happy that i got it now.i need to display job information of a particular server as in enterprise manager can you please help me out in this

[gp-comments width="770" linklove="off" ]