Introduction to sp_refreshview?
sp_refreshview is a system procedure used to update the metadata for the specified view those are not schema bounded.
The definition of sp_refreshview is as below:
create procedure sys.sp_refreshview @viewname nvarchar(776) as declare @ret int exec @ret = sys.sp_refreshsqlmodule_internal @viewname, N'OBJECT', 1 -- viewonly return @ret
Why do we need sp_refreshview?
If your underlying object (table/view) has a modification( we will look into the type of modification sometime later), the metadata of the view needs to be updated to take the effect of the changes unless the VIEW has been sepcified with SCHEMABINDING. To update the metadata, we need to either recreate the view(drop+create) OR need to refresh the view with sp_refreshview system procedure.
Lets look at an example.
--create a Base Table Create Table ParentTable(EmployeeID int, EmployeeName Varchar(50)) Insert into ParentTable Values(1,'Ben'),(2,'Tom') --create a View Create View ParentView as Select * From ParentTable Select * From ParentView --Make a modification on Table schema Alter table ParentTable add EmployeeAge int Select * From ParentView
Here, we can see that the EmployeeAge is not coming as part of the view as the metadata has not been updated though the table has the column.To update the metadata, we are going to refresh the view using sp_refreshview.
sp_refreshview 'ParentView' Select * From ParentView
Now, we can see the EmployeeAge is part of the View ParentView. We are good…
Interesting part…Do we need to refresh view for all modification?
No. Not always. It depends on your underlying object type. If you have a table as an underlying object, yes, you may need to do refresh view for the modifications.But, if the underlying object is again another VIEW, then we may not do a refresh for all cases.
Lets look at an example:
To make the example easier, We are going to create another view CHILDVIEW on PARENTVIEW as underlying object.
Create View ChildView as Select * From ParentView Select * From ChildView
Let us do a modification on the ParentView by adding a Union All as below.
Alter View ParentView as Select * From ParentTable Union All Select * From ParentTable Select * From ChildView
Here, we can observe that even there is a change in the ParentView,ChildView works as expected without a refresh.
Below, I compiled a small table for various types of changes. Of course,the table does not cover all scenarios. You can test for any specific case and confirm further.
Hope, you enjoy this post, please have your thoughts as part of our learning and sharing…..