Retrieving SQL Server 2005 database information using SMO: properties of database objects - How to retrieve all properties of a particular SQL Server View using SMO
(Page 3 of 4 )
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
Next: How to retrieve all properties of a particular SQL Server column using SMO >>
More MS SQL Server Articles
More By Jagadish Chaterjee
