Refresh All Views

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*******************************************************
Example	: EXECUTE [dbo].[usp_RefreshViews] 'vw_Employees'
		  EXECUTE [dbo].[usp_RefreshViews]
*******************************************************/

ALTER PROCEDURE [dbo].[usp_RefreshViews]
(	
	@varViewName		VARCHAR(255)	= NULL
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @varSQL		VARCHAR(4000)

	IF	OBJECT_ID('tempdb..#tmpViews') IS NOT NULL
		DROP TABLE #tmpViews

	CREATE TABLE #tmpViews ( ViewName VARCHAR(255) )

	SET	@varViewName	= COALESCE( @varViewName, '' )

	SET @varSQL = 'SELECT  [name] FROM sysObjects '
				+ 'WHERE	xType  = ''V'' AND '
				+ '			[name] NOT IN (''syssegments'', ''sysconstraints'') '
				+ ( CASE WHEN @varViewName = '' THEN '' ELSE ' AND [name] = ''' + @varViewName  + ''' ' END )

	--PRINT @varSQL
	INSERT		#tmpViews( ViewName )
	EXECUTE		( @varSQL )

	SET	@varViewName =''

	DECLARE curV CURSOR
	FOR SELECT ViewName FROM #tmpViews
	OPEN curV
	FETCH NEXT FROM curV INTO @varViewName

	WHILE @@FETCH_STATUS = 0 
	BEGIN
		PRINT 'Refreshing View...	' + @varViewName
		SET @varSQL = 'EXECUTE sp_refreshView ' + @varViewName
		EXECUTE (	@varSQL )
	
	FETCH NEXT FROM curV INTO @varViewName
	END

	CLOSE curV
	DEALLOCATE curV

	IF	OBJECT_ID('tempdb..#tmpViews') IS NOT NULL
		DROP TABLE #tmpViews

END