Use SQL-DMO to manage SQL Server views and stored procedures
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, page 24, 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, page 24, 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, page 25, 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.)
Prev. page
[1]
2
next page 
