Altering and Dropping Views

3 4

You drop and alter views by using either Enterprise Manager or T-SQL commands. The Enterprise Manager method is easier, as it is when you carry out other SQL Server procedures, but T-SQL commands provide repeatability. In this section, we'll look at both methods.

Using Enterprise Manager to Alter and Drop Views

To alter and drop views by using Enterprise Manager, follow these steps:

1. In Enterprise Manager, expand the Databases folder on the desired server, expand the database containing the view you want to drop or alter, and then click Views to display the views in the right pane of the window, as shown in Figure 18-17.

   

   Figure 18-17. Displaying views in the Enterprise Manager window.

2. Right-click the name of the view you want to modify or drop. A shortcut menu appears, as shown in Figure 18-18. To delete a view, choose Delete from this menu. To alter a view, choose Design View.

   

   Figure 18-18. The shortcut menu for the selected view.

3. If you choose Delete, the Drop Objects dialog box appears, as shown in Figure 18-19. Click Show Dependencies to view the underlying structure of the view. Here you will see what tables the view depends on. If the selected view is a join or a union view, you will see all of the tables involved; if it is a column or a row view, you will see only one table. When you are ready to drop the selected view, click Drop All in the Drop Objects dialog box.

   

   Figure 18-19. The Drop Objects dialog box.

   If you choose Design View from the shortcut menu, the Design View window appears, as shown in Figure 18-20. Notice the similarity to the New View window we saw in Figure 18-10. You can use the Design View window to modify your view in the same way you used the New View window to create the view.

   

   Figure 18-20. The Design View window.

4. Once you have made any necessary modifications to the view, close the Design View window by clicking the window's Close button. You will then be prompted to save the view.

When you have finished modifying the view, you can set the view's permissions. First open the View Properties window by right-clicking the view name in Enterprise Manager and choosing Properties from the shortcut menu. Then click Permissions to display the permissions for the view. The process of setting permissions is described in detail in Chapter 34.

As you can see, modifying a view by using Enterprise Manager is easy. However, if you are modifying or dropping a large number of views, T-SQL can be much more convenient to use because it can be scripted.

Using T-SQL to Alter and Drop Views

To alter views by using T-SQL, use the ALTER VIEW command. The ALTER VIEW command is similar to the CREATE VIEW command and has the following syntax:

 ALTER VIEW view_name [(column, column, ...)] [WITH ENCRYPTION] AS your SELECT statement [WITH CHECK OPTION] 

The only difference between the ALTER VIEW and CREATE VIEW commands is that the CREATE VIEW command will fail if the view already exists, whereas the ALTER VIEW command will fail if the named view does not exist. (The optional keywords WITH ENCRYPTION and WITH CHECK OPTION are covered in the section "Using T-SQL to Create a View" earlier in this chapter.)

To see how the ALTER VIEW command works, let's return to our partitioning example (introduced in the section "Partitioning"). To drop an older partition and add a new partition, we could alter the view as follows:

 ALTER VIEW partview AS SELECT * FROM table_2 UNION ALL SELECT * FROM table_3 UNION ALL SELECT * FROM table_4 UNION ALL SELECT * FROM table_5 

The modified view will look much like it did before the ALTER VIEW command was run, but now a different set of data will be selected. The view no longer uses table_1 and now uses table_5.

To drop a view, use the DROP VIEW command. The syntax of the DROP VIEW command, which is quite simple, is shown here:

 DROP VIEW view_name 

As you can see, the Enterprise Manager and T-SQL command methods are both straightforward and easy to use. Choose the technique that best suits your needs.