Refresh all views in SQL Server database

One problem I’ve had recently is a SQL Server instance in which the views were constantly cached and needed to be refreshed. Normally to do this you would run the build in procedure:

exec sp_refreshview MyView

However – I had the need to run this on all my views daily, and we constantly had new views. You can use the stored proc below to simply loop all the views and run as needed.

create procedure usp_refreshview as

declare @sqlcmd nvarchar(max) = ''

select @sqlcmd = @sqlcmd + 'exec sp_refreshview ' + '''' + name + '''; '
from sys.objects as so
where so.type = 'V'

print @sqlcmd

if len(@sqlcmd) > 0
	exec(@sqlcmd)

To run, simply exec:

exec usp_refreshview