Refresh View Definition by using sp_refreshview in SQL Server

In this post we will learn how to refresh the definition of a view. When you use Select * in the view definition and columns will be added to base table/s later, the view will present the columns those were available at point of view creation. It will not include newly columns even you are using "*" in your query.


To include those columns in the view, we need to refresh the view definition after adding column/s to table/s. Below script can be used to learn step by step how this works and finally refresh the view by using sp_refreshview stored procedure.



If you will drop the column from base table/s tables , you will get an error as you will see at the end of post. Your view will not work without refreshing it.


--Create Sample Table for view
CREATE TABLE dbo.Employee
(id INT IDENTITY(1,1), 
FName VARCHAR(50),
LName VARCHAR(50))
GO
--Insert records in SQL Server Table 
INSERT INTO dbo.Employee VALUES('Aamir','Shahzad')
GO
INSERT INTO dbo.Employee VALUES ('Bob','Ladson')
GO
--Create view to get all columns and all records from dbo.Employee table
CREATE VIEW dbo.vw_Employee
AS
SELECT * FROM dbo.Employee

--See data from dbo.vw_Employee View
SELECT * FROM dbo.vw_Employee

--Alter the base table that we used in the view
ALTER TABLE dbo.Employee
ADD StreetAddress VARCHAR(100)

--Check if we are getting StreetAddress column in dbo.vw_Employee
SELECT * FROM dbo.vw_Employee

--Refresh the View Definition
EXEC sp_refreshview 'vw_Employee'

--Check if column is appearing in view correctly
SELECT * FROM dbo.vw_Employee


If we drop the column from Base Table/s , we will get error. In case, we add or drop column to base table that is used by view, we need to refresh the view definition.

--drop column from base table
ALTER TABLE dbo.Employee
DROP COLUMN StreetAddress  


--Run the Select statement on view to get data after dropping column from base table
SELECT * FROM dbo.vw_Employee

We will get this error
Msg 4502, LEVEL 16, State 1, Line 49VIEW OR FUNCTION 'dbo.vw_Employee' has more COLUMN names specified than columns defined.


--Refresh the View Definition
EXEC sp_refreshview 'dbo.vw_Employee'

--Check if column is removed and view is working correctly
SELECT * FROM dbo.vw_Employee