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