Script to Refresh All Views in a SQL Server Database

Hi guys,

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!

Would you like to check the Portuguese version?
Click on the Brazilian flag ->