SQL Server sp_refreshview to Update View Definition
If sql definition of a view in SQL Server is outdated because the dependent objects have been altered, it is required to update view definition using sp_refresh SQL Server system procedure.
If SQL Server database administrators or Transact-SQL developers have created SQL View objects which are not schema-bounded, if the dependent database object (table, view or function) of the SQL view changes the definition of the SQL Server view itself should be also updated.
The sql syntax of the sp_refreshview SQL Server stored procedure is as follows:
EXECUTE sp_refreshview [ @viewname = ] 'viewname' -- sp_refreshview syntax
Execute sp_refreshview 'MySampleSQLView' -- as an example
Let's make an example and see why Transact-SQL developers require to refresh SQL views in their databases. First we'll create a sql table in our development database. I choosed to create table named ViewTable with four columns in it. After the table creation is completed, insert some test data into sample database table as seen it the below sql script.
After table is created and sample data is generated, create SQL view using "Create View" command with "Select * from ViewTable" sql statement as the view code.
-- Create sample table for SQL view object
Create Table ViewTable (
id int identity(1,1),
viewname sysname,
description nvarchar(max),
active bit
)
go
-- Insert sample data into SQL table
insert into ViewTable select 'SQLView1','Sample SQL View',1
insert into ViewTable select 'SQLView2','Example T-SQL View',1
go
-- Create Transact-SQL View in SQL Server
Create View ViewTableList
as
select * from ViewTable
go
After sql view ViewTableList is created, run below two SELECT statements. Please note that the first Select is executed over database table, on the other hand the second Select statement is executed over sql view object.
SELECT * FROM ViewTable -- read data from database table
SELECT * FROM ViewTableList -- read data from SQL view object
And as expected, both select statements return the same data as seen in below screenshot.
Now, we will change database table definition by adding a new column to sql table using ALTER TABLE tablename ADD columnname statement as follows. I'm adding a CreateDate field to the ViewTable.
Alter Table ViewTable Add CreateDate datetime
go
SELECT * FROM ViewTable -- Select data directly from database table
SELECT * FROM ViewTableList -- Select data from non-updated view
Right after I add the new column to the database table, I executed the same two SELECT statements. The first one displays data from database table, and the second SELECT statements displays data using SQL view.
SQL view object does not return the CreateDate field from the underlying database table
As SQL developers will easily realize, the SQL view does not display the correct data because the sql definition of the view is not updated after the dependent database table definition is updated. The structure of the database table is altered. On the other hand, unaware of this change the view definition is not changed.
So in order to reflect the changes on the SQL Server view definition, Transact-SQL developers can use the system stored procedure sp_refreshview to update view definition.
Execute sp_refresh 'ViewTableList' -- refresh view definition in SQL Server