SQL-DMO Picks Up Where Enterprise Manager Leaves Off

If you spend any time developing database applications with SQL Server 2000 or 7.0, you're familiar with Enterprise Manager, which lets you perform a wealth of server- and database-related tasks with point-and-click convenience. However, as useful as it is, Enterprise Manager lacks some functions, such as the ability to search databases for tables, columns, and other entities by their specific names.

You can use Enterprise Manager to manually browse through your tables and other entities, physically looking for a particular name. However, when you need to search several databases, each of which might have hundreds of tables with thousands of columns, this approach becomes tedious and time-consuming. Another option is to execute SQL queries against some system tables, such as syscolumns, specifying your search target in the WHERE clause. You might also use ANSI-standard SQL information_schema views to enable easy querying of database object metadata. Querying these views is preferable to querying system tables for a couple of reasons. First, you'd have to query the sysobjects and syscolumns tables to get the information that the above query returns. And second, Microsoft doesn't guarantee that the system tables will remain the same between versions and service packs.

Although SQL queries work, to get the information you need, you must understand the internal references that SQL Server uses to keep track of its entities and the relationships between them. This approach trades the logistical nightmare of a manual search for the headache of getting deep down into the database wires. Although the information_schema views don't require you to understand the internal references in the system tables, SQL Server's SQL Distributed Management Objects (SQL-DMO) provides a balance between ease of use and powerful object-manipulation capabilities.

SQL-DMO is a COM-compliant type library that lets you programmatically manipulate native SQL Server objects. In fact, Microsoft used SQL-DMO to build Enterprise Manager. You can leverage SQL-DMO's capabilities with any COM-compliant development language, such as Visual Basic (VB), C++, or VBScript. This article uses VB and SQL-DMO to build an advanced search utility that goes beyond Enterprise Manager's capabilities to locate SQL Server entities by name (or partial name). This sample application demonstrates how you can easily use SQL-DMO in your applications.

Getting Started with SQL-DMO

Let's look at what SQL-DMO offers. Figure 1 shows a simplified subset of the SQL-DMO object model, which consists of many objects and collections, representing both administrative activities and database structures. These objects expose properties, methods, and events for your programmatic use, as do all COM components. For example, to programmatically execute a SQL Server backup, you'd use an instance of the SQL-DMO Backup object. You'd set various properties, such as Database and TruncateLog, to specify the sort of backup you want to perform. Then, you'd invoke the SQLBackup method to actually back up the database. While the backup is running, your program might use the PercentComplete event to display a dynamic status indicator. You can invoke most of SQL Server's administrative functions in this way, including starting and stopping servers, executing jobs, and configuring replication.

Manipulating or examining the structure of databases follows a similar process; however, the concept of collections becomes important. You might have multiple registered servers on a computer, each containing multiple databases, each of which contains multiple tables, and so on. Collections gather these entities together into an abstract construct that you can enumerate programmatically. For example, Listing 1, page 64, shows how you can use SQL-DMO to find all the registered SQL Server systems on your computer. First, the code instantiates the SQL-DMO Application object, whose ServerGroups property serves as a pointer to a collection of ServerGroup objects. These objects represent groups of SQL Server systems that you've registered on your computer. Typically, you'll have only one server group, but the collection concept allows any number. Then, for each server group that the code finds, the RegisteredServers property points to a collection of RegisteredServer objects. Finally, the code adds each registered server's name to a list box on a VB form. The process is quite straightforward.

For the SQL-DMO search application, you need two VB forms. Figure 2 shows the login form, which gathers users' login information for the selected SQL Server instance registered on their system. Figure 3 shows the main search form, which accomplishes most of the work. The application gives users a text box in which they can specify what to look for, a series of check boxes that let them narrow or broaden the scope of entities included in the search, and a list view where they can specify which databases to search. The application then uses a tree view to display the search results in a hierarchical structure to the user.

Logging In to SQL Server

The login form is simpler than the search form and is the first form that application users see. Start by launching VB and creating a new project. Then, before you begin any form design, set a reference to the SQL-DMO type library by selecting references from the Project menu, then selecting Microsoft SQLDMO Object Library from the resulting list. Click OK, then continue with the login form. Change the form's name to frmLogin, and add the controls that Table 1 shows, setting their properties as indicated and positioning them as Figure 2 shows.

After your login form looks like Figure 2, you need to make sure it behaves correctly. The VB code for the login form is quite simple, as Web Listing 1 shows. (See the More on the Web box, page 66, for download information.) The Cancel button, for example, unloads the form, reclaims some memory, and exits the program. The Authentication event for the option buttons determines programmatically whether to enable the User ID and Password text boxes. The Login button, despite its name, doesn't actually log in to the specified SQL Server instance. The Login button simply ensures that the user has selected a SQL Server instance from the drop-down list, then hides the login form and displays the search form.

The Form_Load event's code first declares the variables that it will need: dmoApp to represent the SQL-DMO Application object, dmoServerGroup to represent a ServerGroup object, and dmoServer to represent a RegisteredServer object. The code instantiates the Application object, then adds the keyword "(Local)" to the servers' drop-down list. This keyword, like the equivalent expression ".", represents the SQL Server instance that has the same name as the local computer. Next, the code loops through the available server groups (you'll usually have only one) and iterates through each registered server in each group. For each server it finds, the code adds the server's name, which the Name property exposes, to the server drop-down list. Then, the code sets the drop-down list's Text property to "(Local)" so that the list will be the default server selection for login purposes. Finally, the code cleans house by setting the object variables to Nothing and reclaiming the memory they used.

Getting Down to Business

Because the bulk of SQL-DMO activities take place in the main search form, I put the login process there as well to keep all the more important object declarations together. Although the login form doesn't actually log in to the server, it collects information from the users about which server they want to connect to. The search form does the work.

To build the search form, add a new form to the project (select Add Form from the Project menu). Name the form frmDMO, and add the controls that Table 2 shows. Set the properties for these controls, and arrange them as Figure 3 shows. For this sample application, I've also used an ImageList, together with several icons captured from Enterprise Manager (you can download these icons; see the More on the Web box for instructions). You can do the same, but the search form doesn't require it. When you're finished, open the form's code window and download the code that Web Listings 2, 3, and 4 show. Unlike the login form, the search form's code is meaty.

The first thing to notice in the VB code for the search form in Web Listing 2 is that the code sets Option Explicit and Option Compare Text. Option Explicit (a directive that requires you to declare all variables and constants before you use them) is just good coding practice, but Option Compare Text has a specific use in this code. Because the names of SQL Server entities often have an unpredictable case mixture, Option Compare Text lets you ignore case when comparing strings. After these two directives, the code declares some variables and constants. Most important is the dmoServer variable, of type SQLServer. This variable is the root of the objects' hierarchical tree. The array and constants keep track of entity icons and descriptions.

After the login screen disappears and the search form opens, the search form's load event fires, executing the Form_Load event procedure. Then the search form's code takes four actions. First, the code sets the values of the entity description array, which helps generate status messages in the status bar. Second, the code initializes an ImageList control and loads several icons (like those I mentioned earlier) into it. Third, the code instantiates the dmoServer object for immediate use. And fourth, the code invokes the procedure EnumerateDatabases.

The purpose of the EnumerateDatabases subroutine is to connect to the selected server and populate the database selector listview control with the names of all the databases on the server. First, the subroutine needs a Database object, dmoDatabase; then, EnumerateDatabases needs to connect to the server. Next, the subroutine checks to see whether the user selected Windows NT Authentication or SQL Server Authentication on the login form. If the user chose NT Authentication, the subroutine sets the LoginSecure property of the global SQLServer object, dmoServer, to True. Then, for both NT Authentication and SQL Server Authentication selections, the subroutine calls the dmoServer object's Connect method, passing it the values that the user entered for the SQL Server system, User ID (UID), and password on the login form. (If the user chose NT Authentication, the Connect method ignores the UID and password.) Next, the subroutine uses the For Each loop to enumerate all the databases on the selected server, adding the name of each database that it finds in the dmoServer object's Databases collection to the search form's listview control. Finally, the subroutine returns the dmoDatabase object to Nothing.

After the EnumerateDatabases subroutine, the code uses several small event procedures, three of which correspond to click events on the form's command buttons. The cmdClear button's code calls the Nodes collection's Clear method to clear the Search Results treeview and resets the status bar to a blank value. The cmdExit button's code unloads the form, destroys it, and ends the application. When the code unloads the form from memory, the Form_QueryUnload event fires. Form_QueryUnload performs necessary housekeeping, destroying object variables and exiting the application.

The cmdSearch button's code is the gateway to the search application. The code sets the form's mouse pointer to the hourglass to indicate a potentially lengthy process, calls the PerformSearch() routine, and resets the mouse pointer. The PerformSearch() procedure and its workhorse subroutine, SearchObject, are the application's meat and potatoes.

Searching with SQL-DMO

Web Listing 3 shows the PerformSearch() procedure, which first declares some variables and clears the Search Results treeview. Then, PerformSearch() adds a root node to the treeview so that the procedure will have a node to attach the results to. The code then sets the root node's Expanded property to True so that the results will be immediately visible within the treeview. Next, the code initializes the intDatabases variable to 0. This variable keeps track of the number of databases that return positive search results.

The code then loops through all the ListItems in the lvwDatabases listview. For each ListItem with the Checked property set to True (meaning that the user wants to search that database), the code adds a node representing the database to the Search Results treeview. The code also sets the database's Expanded property to True to ensure that the database will be visible when the procedure terminates. Then, the code increments the intDatabases counter to show that results might exist for this database.

The PerformSearch() code proceeds through the available object types—tables, columns, stored procedures, views, and users—and searches each object type by calling the SearchObject subroutine. This subroutine accepts three parameters. The second and third are for cosmetic purposes, such as displaying the appropriate icon in the treeview, but the first parameter is pure SQL-DMO. For each object type except columns, a corresponding Database object's property provides direct access to the collection of this type of objects. The SearchObject code accesses the Database object as an indexed member of the global dmoServer object's Databases collection. The code drills down to the appropriate database by referring to the member of the dmoServer.Databases collection that the intCounter loop variable indexes in the lvwDatabases listview. Each Database object contains a Tables collection, a StoredProcedures collection, a Views collection, and a Users collection. The code passes a reference to each of these collections to the SearchObject subroutine for processing. If the user has also elected to search columns, the code will pass the parent Tables collection and process this collection differently.

The SearchObject subroutine adds nodes to the treeview for each successful search result. Upon SearchObject's return, PerformSearch() must determine whether the subroutine added any results nodes to the treeview for the current entity type. If not, the code removes the node it added earlier for this database and decrements the count. Next, the code looks to see whether the subroutine got any results, for any of the entity types. If the root node has no children, SearchObject didn't find anything that matched the search target. In this case, the code tells the user that the search found no matches. If the subroutine did get results, the code tells the user that the search completed successfully and reports how many matching entity names the subroutine found. To calculate the number of matches, the code takes the total number of nodes in the treeview, subtracts 1 for the root node, and subtracts the number of nodes representing databases. This process provides a quick count without traversing the treeview.

The SearchObject subroutine, which Web Listing 4 shows, starts by declaring two variables: dmoColumn, of the SQL-DMO type Column, and dmoObject, of the generic VB type Object. However, you can use the same code no matter which type of SQL entity you're looking at. For example, the dmoCollection parameter is type Object, although the parameter passed to dmoCollection might be SQL-DMO type Table, StoredProcedure, View, or User.

SearchObject has two paths: one for columns and one for other SQL entity types. The code for the other types loops through each dmoObject in the dmoCollection, displays some descriptive feedback in the status bar, and performs the comparison. The code uses the VB like operator because like lets you use wildcards and other search expressions. If the object's Name property is like the target search text, then a potential search result exists. Before adding the object to the treeview, however, the code must determine whether the object is a system object and whether the user wants to include system objects. The code makes this determination by examining the SystemObject property of each object in the collection. If this property is True, then you're looking at a system object. If the object meets all criteria, the code adds the object to the Search Results.

The only difference for processing columns is that the Database object doesn't have a direct connection to the Columns collection. The code needs an outer For Each loop to iterate through all the tables in the database and an inner For Each loop to iterate through each column in each table. Otherwise, the logic is essentially the same as for processing other entity types. The code then cleans up its temporary variables and completes. Finally, the Form_QueryUnload event procedure cleans up variables and exits.

A Test Drive

To test the application, set the search target and other options to match the values in Figure 4, then click Search Now. You should see results similar to those in Figure 4, representing the stored procedures, views, table, and column that matched the search target of *sales*. The ability to search all sorts of entities in multiple databases, and to do so with wildcard characters, is a powerful application of SQL-DMO. But this search application is just one of many potential uses for SQL-DMO. You could even build your own "enterprise manager," adding to it all the features you wish Microsoft had included in its Enterprise Manager.