Retrieving SQL Server 2005 Database Info Using SMO: Basics - How do we retrieve “Database” Information about a particular SQL Server instance using SMO?
(Page 5 of 5 )
The previous code just introduced you to connecting to an instance using SMO. The information that we retrieved in the previous code is not of much practical use. Let us further extend the same solution with one more “windows form” which retrieves all “database” related information present in a particular SQL Server database instance.
Don’t forget to add a new form (such as “form2”) to your existing solution and modify your project properties so that it will be the “start up form” during execution. The layout of the form will be very similar to that of the one I designed in the previous section.
Open a code window (by pressing F7) and modify the code in such a way that it looks something this:
Imports Microsoft.SqlServer.Management.Smo
Public Class Form2
Private Sub btnShow_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnShow.Click
Dim svr As Server = New Server(".\sql2k5")
Dim db As Database
For Each db In svr.Databases
Me.TextBox1.Text &= db.Name & ControlChars.NewLine
Next
End Sub
End Class
The only new part within the above code is this:
Dim db As Database
For Each db In svr.Databases
Me.TextBox1.Text &= db.Name & ControlChars.NewLine
Next
“Microsoft.SqlServer.Management.Smo.Databases” is a collection; it contains the list of all databases existing within the connected instance. You can retrieve each of those using a loop. For every iteration, the loop fetches a particular “database” into “db.” We extract information about that “database” using its properties and finally add the same to the textbox.
Summary
In this article, I touched only the basics of SMO available in Microsoft SQL Server 2005. You can explore further by referring to SQL Server 2005 books online at Microsoft's website. You can also develop your own web-based SQL Server database management tool using ASP.NET 2.0 without changing the code at all. In fact, it all works with .NET.
You can extend the possibilities even further to Smart Clients such as Smart Phone, Pocket PC, Tablet PC, and so on. If you are familiar with WMI, you can extend the possibilities to that level as well.
For your practice, you can try to develop a simple Backup/Restore utility using SMO together with Visual Basic 2005. I already contributed an article on that subject, but with SQL-DMO (in SQL Server 2000). You can check it out at here: http://www.aspfree.com/c/a/VB.NET/Simple-BackupRestore-Utility-With-SQLDMO-
Using-VBNET/1/
My forthcoming articles (related to this subject) will focus on in-depth discussion along with developing some small practical utilities (which may be essential in certain situations). So, do not forget to check this site frequently :)
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. |
