Dunk

-- sp_refreshview 
-- http://msdn.microsoft.com/zh-tw/library/ms187821(v=sql.90).aspx
-- RefreshAllView
-- http://www.sqlservercentral.com/Forums/Topic429031-559-1.aspx#bm1319011

USE [使用者資料庫]
GO

/*
自動更新資料庫(database level)所有的view
用法: exec dbo.uspRefreshViews
(SQLSERVER2000適用)
*/
IF EXISTS
(SELECT name
 FROM
	 sys.procedures
 WHERE
	 name = 'uspRefreshViews') DROP PROCEDURE uspRefreshViews
GO
create proc dbo.uspRefreshViews
as
begin
SET NOCOUNT ON 
		declare @views table
	(	
		id int identity(1,1) not null,
		schemaName NVARCHAR(128),
		viewName nvarchar(128) not null
	);
declare @maxID int,
		@id int ,
		@aViewName nvarchar(128) ,
		@aSchemaViewName nvarchar(258);
SET @id = 0;
SET @aViewName = '';
INSERT INTO @views
SELECT sch.name
	 , vw.name
FROM
	sys.views vw
	INNER JOIN sys.schemas sch
		ON sch.schema_id = vw.schema_id

SELECT @maxID = coalesce(@@ROWCOUNT, 0)

	while @id <> @maxID
	begin
SELECT TOP 1 @id = id
		   , @aSchemaViewName = schemaName + '.' + viewName
FROM
	@views
WHERE
	@id < id
ORDER BY
	id ASC			
	print 'Refreshing '+@aSchemaViewName
EXECUTE sp_refreshview @aSchemaViewName
	end
end
GO

/*
自動更新資料庫(database level)所有的view
用法: 
To Run the procedure for all views type:
 exec dbo.refviews 

To Run the procedure for certain view (Ex. view "VWMMS_MR") type :
 exec dbo.refviews EMPLOYEEMAP
(SQLSERVER2005以上適用)
*/
IF EXISTS
(SELECT name
 FROM
	 sys.procedures
 WHERE
	 name = 'refviews') DROP PROCEDURE refviews
GO

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

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;
go