SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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
-- 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
-- Create Transact-SQL View in SQL Server
Create View ViewTableList
select * from ViewTable

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.

update view definition in SQL Server

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
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.

refresh SQL Server view definition using sp_refresh stored procedure

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

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.