Views need to be refreshed if the underlying tables change at all. You shouldn’t have to run sp_refreshview for altering a view. Only for altering its underlying tables.
The following T-SQL script can be used to refresh all view inside a database:
--USE YOUR_DBNAME DECLARE views_cursor CURSOR FOR SELECT name FROM sysobjects WHERE type = 'V' OPEN views_cursor DECLARE @view NVARCHAR(500) FETCH NEXT FROM views_cursor INTO @view WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXEC sp_refreshview @view END TRY BEGIN CATCH PRINT 'VIEW NAME: ' + @view + ', ERROR NUMBER: ' + Cast(ERROR_NUMBER() as VARCHAR) + ', ERROR MESSAGE: ' + ERROR_MESSAGE() END CATCH FETCH NEXT FROM views_cursor INTO @view END CLOSE views_cursor DEALLOCATE views_cursor
Note that the script uses a cursor to go through all views of a database, it also handle possible errors giving view name, error number and error message.
Click on the following link if you want to know more about sp_refreshview.
Click on the following link if you want to know more about ERROR_NUMBER (Transact-SQL).
Click on the following link if you want to know more about ERROR_MESSAGE (Transact-SQL).
Thanks for reading.
Like and Share if you found it may be useful to someone you know!