Check when the view definition was refreshed in SQL Server

View is a virtual table. It doesn’t store the data and does not physically exist in SQL Server. When we calling the view it will fetch the data from the table and display it.  View can be made over one or more database tables. Generally we put those columns in view that we need to retrieve/query again and again. Once we have created the view, we can query view like as table. We can make index, trigger on view.

ReadView in sql server:

http://bageshkumarbagi-msbi.blogspot.in/2016/06/view-in-sql-server.html

Suppose we change the structure of the table then we need to refresh the view. For refresh the view definition we can use sp_refreshview to update metadata for columns used in the view.

See the example.

I am using Emp table

I am create view

create view vEmp As select * from Emp

View created successfully.

Now I am adding a new column in the Emp table.

ALTER TABLE Emp ADD Email VARCHAR(100)

Column added successfully.

Without refreshing the view I am going to run the view.

Update value we are not getting.

We need to refresh the view definition. We can refresh view by two way.

Ø  Alter view

Ø  sp_refreshview

Alter view

ALTER view vEmp As select * from Emp

SP_refreshview

EXEC sp_refreshview 'vEMP'

View is refreshed. Now see the value.

Suppose I am dropping the column from table

ALTER TABLE Emp DROP COLUMN Email

Column Email is dropped successfully. Now I am getting value from view without refreshing view.

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.

Now referring the view

We will get the data.

Sometime we need to know what was the last time the view definition was altered or refreshed. Using below sql script we will get.

SELECT         [name]        ,create_date        ,modify_date FROM         sys.views

See the output.