Presents your  
SQL SERVER E-NEWSLETTER for September 16, 2003 
 
<-------------------------------------------> 
  
USE ALTER VIEW TO MAKE VIEW CHANGES   
  
If you need to change or update a view and maintain the current 
dependent stored procedures and triggers, use ALTER VIEW. Even though it may
be tempting to drop and recreate your view, doing so may essentially erase 
any permissions, stored procedures, or triggers on the view.   
  
The syntax for ALTER VIEW is almost the same as CREATE VIEW and, like 
CREATE VIEW, you can alter a view by adding WITH ENCRYPTION and/or WITH 
SCHEMABINDING.   
  
There are, however, several caveats with ALTER VIEW. If your view 
accesses new objects, your view permissions must have the same
permissions as the new object or your view will fail.   
  
In addition, the user altering the view also must have ALTER VIEW and 
SELECT permissions on the tables, views, and table-valued functions being 
referenced in the view, as well as any EXECUTE permissions on 
scalar-valued functions being called in the view.   
  
Also keep in mind that if you originally created the view with WITH 
ENCRYPTION or with a CHECK OPTION, you'll have to reinclude these arguments 
in the ALTER VIEW statement. In other words, if you modify the view, you
have to reset the WITH ENCRYPTION and CHECK OPTION. These arguments
won't automatically carry over.   
  
In the examples below, I'm going to create a simple view and run it. 
Then, I'll modify it, confirm its results, and use sp_helptext to look at 
the view text to see the changes.   
  
First, I'll create a simple view that pulls the first name, last name, 
and city of all the California authors in the authors table.   
  
CREATE VIEW vwCalAuthors   
AS   
Select au_fname, au_lname, city   
FROM authors   
where state = 'CA'   
With CHECK OPTION   
GO   
  
Let's see if the view works.   
  
SELECT * FROM vwCalAuthors   
  
au_fname             au_lname             city            
--------             --------              -------------   
Johnson              White                Menlo Park   
Marjorie             Green                Oakland   
Cheryl               Carson               Berkeley   
Heather              McBadden             Vacaville   
  
(15 row(s) affected)   
  
Now, I decide that I just want this view to reflect authors from Oakland, CA.   
  
ALTER VIEW vwCalAuthors   
AS   
Select au_fname, au_lname, city   
FROM authors   
WHERE state = 'CA' and city = 'Oakland'   
GO   
  
I run the result set and just get authors from Oakland.   
  
au_fname        au_lname        city          
--------        --------        ----           
Marjorie        Green           Oakland   
Dean            Straight        Oakland   
Dirk            Stringer        Oakland   
Stearns         MacFeather      Oakland   
Livia           Karsen          Oakland   
  
(5 row(s) affected)   
  
Finally, if you'd rather call up the text of an altered view instead of 
run the result set, you can use the sp_helptext system stored procedure 
to see what text looks like behind the view. (Remember, the ALTER VIEW is 
changing the CREATE VIEW statement that was initially used to create the
view in the first place so that's the statement you should see in your 
results.)   
  
Notice what else is missing. I didn't include the CHECK OPTION when I 
altered the view and now it's gone. CHECK OPTION and WITH ENCRYPTION
have to be reset manually each time you alter a view.   
  
Sp_helptext vwCalAuthors   
  
Text   
--------------------------------------------------------   
CREATE VIEW vwCalAuthors   
AS Select au_fname, au_lname, city   
FROM authors   
WHERE state = 'CA'and city = 'Oakland'   
  
J.E. Harvey, MCSD, MCDBA, has been tinkering with and writing about 
technology for more than two decades.   
  
----------------------------------------