Retrieving SQL Server 2005 Database Info Using SMO: Database Info, Table Info - How do we retrieve table information from a SQL Server database using SMO?
(Page 5 of 5 )
This section will be a bit different from the previous sections. Here, I need to provide a list of all existing databases as a dropdown. When the user selects a database and clicks on a button, it should retrieve all tables and related information from the selected database.
Just to retrieve the database list, I constructed a separate routine 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
After completing the above routine, I proceeded with the following “form load” event to populate the list of database names into the dropdown.
Private Sub Form6_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim svr As Server = New Server(".\sql2k5")
Me.ComboBox1.DataSource = util.getDatabaseList(svr)
Me.ComboBox1.DisplayMember = "DatabaseName"
End Sub
Okay, the code above mainly covered getting database names into a dropdown list. Now I shall further extend what we are doing to retrieve table information for the selected database.
Before proceeding further, we again needed to define a new structure to hold the table information. Let us look into that.
Public Function getStructTableBasic() As DataTable
Dim dt As New DataTable
With dt.Columns
.Add (New DataColumn("TableName"))
.Add (New DataColumn("Columns"))
.Add (New DataColumn("Indexes"))
.Add (New DataColumn("RowCount"))
.Add (New DataColumn("Triggers"))
End With
Return dt
End Function
This is very similar to the one I defined in the first section. Now, let us proceed with the code to retrieve the information.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button1.Click
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
Dim tbl As Table
Dim dtReport As DataTable = util.getStructTableBasic
For Each tbl In db.Tables
Dim drReport As DataRow = dtReport.NewRow
drReport ("TableName") = tbl.Name
drReport ("Columns") = tbl.Columns.Count
drReport ("Indexes") = tbl.Indexes.Count
drReport ("RowCount") = tbl.RowCount
drReport ("Triggers") = tbl.Triggers.Count
dtReport.Rows.Add (drReport)
Next
Me.DataGridView1.DataSource = dtReport
End Sub
From the above code fragment, I selected only a particular database using the following statement:
Dim db As Database = svr.Databases(Me.ComboBox1.Text)
Summary
Apart from the above information I explained (related to databases and tables), there exists a lot more information you can retrieve from the server, database and table. But for the sake of keeping this article simple, I included only the most important ones.
You can use all of my code and design; think of it as a sort of “SMOLibrary” and reuse it in several applications. If you need any further interesting topics on SMO, please do not hesitate to post.
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. |
/ 12 
