You can simply use this stored procedure to refresh all database views with one command (including dependent views problem handling - Binding errors)

Note : this procedure is examined on SQL Server 2005 .

CREATE procedure [dbo].[refviews] (@varViewNameVARCHAR(500)= NULL )

--Created on 08 Feb 2009 By Ahmed Omara

as

declare @p_viewname nvarchar(500)

BEGIN

IF (@varViewName IS NOT NULL OR @varViewName <>'')

--Validate certain view specified.

BEGIN

SET @varViewName = ('['+@varViewName+']');

PRINT 'Refreshing View...' + @varViewName;

EXEC sp_refreshview @varViewName;

END;

ELSE

BEGIN

--CREATE CURSOR TO READ THE DATABASE VIEWS IN ORDER TO VALIDATE

Declare @CrsrView Cursor

Set @CrsrView = Cursor For

SELECT [name]

FROM sysObjects

WHERExType = 'V'

AND [name] NOT IN ('syssegments', 'sysconstraints')

AND category=0

ORDER BY crdate

-- fetch the first record in the Cursor

Open @CrsrView

Fetch Next From @CrsrView Into @p_viewname

While (@@FETCH_STATUS = 0) --WHILE THE CURSER STILL INCLUDE RECORDS

BEGIN

--refresh the view

SET @varViewName = ('['+@varViewName+']');

PRINT 'Refreshing View...' + @p_viewname;

EXEC sp_refreshview @p_viewname;

-- fetch the next record in the Cursor

Fetch Next From @CrsrView Into @p_viewname

END

Close @CrsrView

Deallocate @CrsrView

PRINT 'Refresh process is done successfully'

END;

End;

/*

To Run the procedure for all views type:

EXEC refviews

To Run the procedure for certain view (Ex. view "VWMMS_MR") type :

EXEC refviews VWMMS_MR

*/