Retrieving SQL Server 2005 database information using SMO: properties of database objects - How to retrieve all properties of a particular SQL Server column using SMO
(Page 4 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. 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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
