Microsoft provides an object-oriented library for dealing with SQL objects: SQL-DMO (SQL Distributed Management Objects). By using this library, you can perform a number of cool tasks that are difficult or impossible to do in T-SQL.
As with all object libraries, you have to learn to walk the hierarchy. The highest-level object in the library is the collection of servers. Below that level are the databases, and the tables, views, sprocs, and so on that reside within a given database. This article will show you the most basic steps on how to connect to a server and its databases using SQL-DMO.
The first thing you need to do is obtain the list of servers. The following code will deliver the list of servers to a listbox:
Function listServers(oControl As Object)
Dim oApp As SQLDMO.Application
Dim oNames As SQLDMO.NameList
Set oApp = New SQLDMO.Application
Set oNames = oApp.ListAvailableSQLServers()
For Each oName In oNames
The next (lower) element in the hierarchy is the list of databases. This is where you may need to supply a userid and a password (if you aren't using integrated security). Note: Be sure to read the following code before using it, particularly the demarcated lines.
Function listDatabases(vServer As String, oControl
Dim oSrvr As SQLDMO.SQLServer
Set oSrvr = New SQLDMO.SQLServer
oSrvr.LoginSecure = True
' use this line for integrated security
' or this for sql login
oSrvr.Connect vServer, "sa", "SQLTips" - substitute your sa password
For Each oDatabase In oSrvr.Databases
I used such code when I was deploying an application written against MSDE, which doesn't include Enterprise Manager, Query Analyzer, and all those other nice tools. With SQL-DMO, I was able to write a small application that enables users to create a new database, install the tables we shipped (mostly blank but some of them were pre-populated), and then load and go.
I encourage you to investigate SQL-DMO further. By descending into the hierarchy, you can walk the list of tables, queries, and so on, using very simple code like the examples above. Once you dive in, it turns out to be very easy, and it can save you lots of time.
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!