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: