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, page 66, 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.