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]
(
@varViewNameVARCHAR(255)= NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @varSQLVARCHAR(4000)

IFOBJECT_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 '
+ 'WHERExType  = ''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

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

END