Tag: update view metadata

  • How to make sure that view metadata is updated?

    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