Problem: How to make sure that view metadata is updated?
Solution: You can use sp_refreshview system stored procedure to make sure that view meta data is updated. See below example for more information.
--creating demo table
create table demo
(
id int identity (100,1),
name varchar(10)
)
-- inserting sample values
insert into demo values ('Jugal'), ('Naresh')
-- creating view on table demo
create view vw_DEMO
as
select * from demo
-- select data from view
select * from vw_DEMO
-- adding new column to base table
alter table demo
add City varchar(10) null
-- select data from view, it is still returning ID and Name column
select * from vw_DEMO
-- Updating view sp_refreshview system stored procedure
sp_refreshview vw_Demo
-- selecting data again from view and it is returning all the columns
select * from vw_DEMO
You can create below procedure to refresh all the views on the database.
create procedure proc_RefreshViews
as
begin
declare @vwName varchar (2000)
declare objCursor cursor for
select name from sys.objects where type = 'V'
OPEN objCursor
FETCH NEXT from objCursor INTO @vwName
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Starting refresh of ' + @vwName
exec ('sp_refreshview ''' + @vwName + '''')
print 'completed refresh of ' + @vwName
print '--------------------------------'
FETCH NEXT from objCursor INTO @vwName
END
CLOSE objCursor
DEALLOCATE objCursor
end