Refresh All Database Views

[aomara]

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] ( @varViewName VARCHAR(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
WHERE xType = '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
*/

 

[genomon]

Why not post as a FAQ?

Cogito eggo sum – I think, therefore I am a waffle.