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.
End of Article
Prev. page
1
[2]
next page -->
