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 4502LEVEL 16State 1Line 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

2 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. When you see the Pizza Tower link, it's time to let your excitement multiply. This site allows you to entertain yourself with a variety of games.

    ReplyDelete