My past two columns provided the techniques you need to programmatically manage SQL Server databases with SQL Distributed Management Objects (SQL-DMO). These techniques are particularly important for Microsoft Access developers and others. Enterprise Manager doesn't ship with the Microsoft Data Engine (MSDE) or the Microsoft SQL Server 2000 Desktop Engine. MSDE 2000 ships with Microsoft Office XP (formerly codenamed Office 10), and MSDE ships with Office 2000. However, SQL-DMO lets you selectively incorporate Enterprise Manager funtionality in your solutions.
In "SQL-DMO: Learning the Basics," April 2001, I introduced the SQL-DMO topic and demonstrated how to build an Access form application to explore SQL Server tables and columns. In "Creating Tables with SQL-DMO," May 2001, I examined how to build tables by adding columns, specifying primary keys, and defining relationships with other tables. In this month's column, I look at the SQL-DMO syntax required to examine and manipulate SQL Server views and stored procedures. You'll learn how to enumerate members of views and stored procedures, rename views and stored procedures, and develop solutions with ADO.
Enumerating Views and Stored Procedures
SQL-DMO represents SQL Server views and stored procedures as collections that are hierarchically dependent on SQL Server database objects. The members of the Views and StoredProcedures collections are View and Stored Procedure objects, respectively. Database objects, such as views and stored procedures, have a read-only SystemObject property that can tell you whether an object is user defined. If Microsoft defines an object that installs as part of SQL Server, the object's SystemObject property is True; if a user defines an object, the object's SystemObject property is False. Most databases have an assortment of user-defined and system-defined objects.
The code in Listing 1 shows how to answer a common SQL Server developer question: What are the names of the user-defined views and stored procedures in a database? This code contains two subprocedures: Caller and ListViewsAndStoredProcedures.
As callout A in Listing 1 shows, Caller sets up the parameters and passes them to the second procedure, which performs a specific task—in this case, listing views and stored procedures. Caller also plays the same role in the other listings; thus, Caller appears with commented-out procedure calls, as callout B in Listing 1 shows. Subsequent listings show only the second procedure, and Caller invokes the code by removing the appropriate comment indicator from Listing 1.
ListViewsAndStoredProcedures connects to the NorthwindCS database, or you can use another server database that contains both views and stored procedures. Callout C in Listing 1 shows the syntax for processing the members of the Views collection. The first line prints a collection header. Then, the code loops through the Views collection members and prints only the names of the user-defined views (i.e., the code verifies that the SystemObject property is False). Callout D in Listing 1 highlights the code that first prints a blank line to separate the views from the stored procedures, then loops through the StoredProcedures collection members and prints only the names of the user-defined stored procedures.
Using ADO to Rename Views
SQL-DMO doesn't have a method for renaming views. However, you can develop a custom rename procedure by using SQL-DMO alone or with ADO. Looking at the differences between these two approaches will help explain how ADO and SQL-DMO differ. Listing 2 shows the RenameViewWithADO procedure, which requires the server name, login name, password, and database name as arguments; RenameViewWithADO also needs the view's current name (oldname) and what you want to rename it (newname). You can use the code at callout A in Listing 1 to call RenameViewWithADO by removing the comment indicator from the call line in callout B of Listing 1.
RenameViewWithADO creates two T-SQL strings: the first to remove the old view and the second to create the new view, as Figure 1 shows. To keep the syntax simple, I created a custom view that joins the Categories and Products tables from the NorthwindCS database. If you don't have the NorthwindCS database, you can add my custom view to the Northwind database. (For download information, see the More on the Web box.) After making the database connection, RenameViewWithADO generates T-SQL strings with SQL-DMO and edits those strings so that ADO can use them with the built-in Replace() function. No T-SQL strings appear in RenameViewWithADO. The procedure creates and edits a script to remove the view's initial version, as callout A in Listing 2 shows. The SQLDMOScript method writes the script, which includes a trailing GO keyword for Query Analyzer—an invalid statement for ADO. So, Replace() removes the trailing GO at callout A in Figure 1, which shows the automated script that the SQLDMOScript method generates both before and after Replace().
Next, RenameViewWithADO generates and edits the T-SQL code needed to rename the view, as callout B in Listing 2 shows. The SQL-DMO Text property of the Views object returns the T-SQL code required to create the view, including the CREATE VIEW statement and its embedded SELECT statement, as callout B in Figure 1 shows. CREATE VIEW requires editing to reflect the view's new name. The Replace() function transforms the T-SQL code so that it creates a view with the new name. (The code at callout B in Figure 1 shows the before and after versions of the CREATE VIEW statement. Note that the created view's name changes from View1 to View2.)
You could also rename a view by using ADO to call the sp_rename system stored procedure, as the following code shows:
'ADO statement to change the view name str1 = "sp_rename view2, view1" then execute str1
After RenameViewWithADO generates the desired SQL strings, the procedure instantiates an ADO Connection object to execute the SQL strings. This instantiation occurs in a With...End With statement. (Note that ADO and SQL-DMO can't share connections.)
Using Only SQL-DMO to Rename Views
You can also rename views by using only SQL-DMO. The RenameViewWithoutADO procedure, which Web Listing 1 shows, renames a view without resorting to ADO, so you don't have to create an ADO connection if you already have a SQL-DMO database connection. In addition, if you're using SQL-DMO, your SQL script doesn't need to remove the view with the old name because SQL-DMO includes a Remove method that can discard database objects, such as views.
The overall strategy for using SQL-DMO to rename the view differs slightly from the ADO approach. The SQL-DMO code in RenameViewWithoutADO instantiates two View objects: one for the old name and the other for the new name. Then, the procedure drops the first view and adds the second one to the database.
Backing Up a Stored Procedure
Conceptually, backing up a database object is similar to renaming it. In either situation, you create and store a copy of a database object. However, when you back up an object, you don't delete the original object as you do when you rename an object.
The backup example that the code in Listing 3 shows works with a stored procedure to compare the differences between how SQL-DMO processes views and stored procedures. The original and backup objects are the same, except that the backup procedure appends "bu" to the stored procedure's name. I developed this example by using the Ten Most Expensive Products stored procedure, which Microsoft ships with both the NorthwindCS and Northwind databases. The CREATE PROCEDURE statement includes double quotes because the procedure's name contains embedded blank spaces. Because, in general, square brackets are SQL Server's default delimiter for identifiers with embedded blanks, the sample code replaces the double quotes with square brackets for the stored procedure's name. Failing to make this replacement can cause a runtime error.
The code at callout A in Listing 3 starts to create the backup copy. Stored procedures have Name and Text properties, just like views do. However, for a backup, you must remove the backup's previous version, if it exists. If you don't remove the previous version, you'll generate a runtime error when you add the new backup to the database. If no previous version exists, the On Error Resume Next statement proceeds to the next line of code and restores normal error processing.
The code at callout B in Listing 3 replaces the double quotes with square brackets by dividing the CREATE PROCEDURE and its SELECT statement into two parts, str1 and str3, which exclude the name in double quotes. Then, the code inserts the new name (in square brackets) between str1 and str3 by concatenating the CREATE PROCEDURE and SELECT statements. The code at callout C in Listing 3 adds the new backup procedure to the database.
This third column completes my series introducing SQL-DMO programming. SQL-DMO is available for any programming environment that can control a COM object, so these topics have broad applicability. Using the information I've presented, you can enumerate SQL Server objects and their properties, build tables and specify their relationships to other tables, and manage views and stored procedures. You can also directly control access to administrative capabilities from custom applications by using SQL-DMO in your development environment.
