Wednesday, November 02, 2005

Sp_refreshView explained ...

Often people ask me "I have a table and there are few views based on that table. When I make a structural change to my table it invalidates all those views. So we are left out with no other option than to drop those views and recreate it. But is there any alternate way for this?". For all those people who have this doubt in mind .. read on.

i) Create this sample table for demo purpose

Create table tstTestingUpdateView
(
Sno int identity,
[Name] varchar(10),
Mail varchar(50)
)

ii)Insert some dummy records

Insert into tstTestingUpdateView Values('Vadivel','smart3a@yahoo.com')

iii) Create a view based on that table

Create view tstView1
As
Select * from tstTestingUpdateView


iv) Execute the newly created view and have a look at the output

Select * from tstView1

v) Now add a new column to the table

Alter table tstTestingUpdateView add ContactNumber Varchar(20)

--Now if you execute the view it won't list the newly added column in it
Select * from tstView1

vi) Solution

--For that make use of the below system stored procedure
sp_refreshview tstView1

--Now exceute the view to see the newly added column
Select * from tstView1

vii) Clean up

Drop view tstView1
Drop table tstTestingUpdateView

No comments: