How to programmatically manipulate native SQL objects
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, page 64, 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.