Refresh All Views

After a schema change, views can go stale - and finding out one by one through application errors is no fun. This stored procedure walks every view in the database and runs sp_refreshview on each, catching errors along the way. It prints only the failures that are not the harmless "invalid for this operation" message (views referencing objects that do not exist yet), so you get a clean list of what actually needs attention.

ALTER PROCEDURE [dbo].[RefreshViews] AS
BEGIN
	DECLARE @Name VarChar(8000)
	DECLARE @SQL VarChar(8000)

	DECLARE Views CURSOR  FAST_FORWARD FOR
		SELECT
			name,
			'EXEC sp_refreshview ''' + name + ''''
		FROM
			sys.objects
		WHERE
			type = 'v'
	OPEN Views

	FETCH FROM Views INTO @Name, @SQL

	WHILE(@@FETCH_STATUS = 0)
	BEGIN--WHILE
		--------------------------------------------------------------------------------------------

		BEGIN TRY
			EXEC(@SQL)
		END TRY
		BEGIN CATCH
			IF ERROR_MESSAGE() NOT LIKE '%invalid for this operation%'
			BEGIN--IF
				PRINT @Name + ' --> ' + ERROR_MESSAGE()
			END--IF
		END CATCH

		--------------------------------------------------------------------------------------------
		FETCH FROM Views INTO @Name, @SQL
	END--WHILE

	CLOSE Views DEALLOCATE Views
END
GO