Refresh all Views

print char(13) + char(10) + 'Begin Refresh all Views'

GO

declare @fullname varchar(4000)

-- declare & open a cursor to get the names of our views.
DECLARE curViews cursor fast_forward for
   select nm = OBJECT_NAME(a.[Object_id])
      , sch = OBJECT_schema_name(a.[Object_id])
   from sys.all_views a
   where a.[name] like 'myviewprefix%'	-- update this filter as necessary
OPEN curViews

-- declare some variables to hold the data from the cursor
declare @name sysname
   , @schema sysname

-- get the 1st record from the view.
FETCH next from curViews into @name, @schema

-- keep going while the last fetch was good
WHILE (@@FETCH_STATUS = 0)
begin
   -- build our full object name, including the schema
   set @fullname = @schema + '.' + @name

   -- refresh the view
   print 'Refreshing view: ' + @fullname
   EXECUTE sp_refreshview @fullname;

   EndLoop:
   -- get the next record from the cursor
   FETCH next from curViews into @name, @schema
end

-- close & deallocate the cursor
CLOSE curViews
DEALLOCATE curViews

GO

print char(13) + char(10) + 'Refresh all views completed at ' + convert(varchar, getdate(), 109)
   + char(13) + char(10) + 'Server Name: ' + @@servername
   + char(13) + char(10) + 'Database: ' + db_name()
   + char(13) + char(10) + char(13) + char(10)

DECLARE @sqlcmd NVARCHAR(MAX) = ''
SELECT @sqlcmd = @sqlcmd +  'EXEC sp_refreshview ''' + name + ''';
' 
FROM sys.objects AS so 
WHERE so.type = 'V' 

print @sqlcmd

EXEC(@sqlcmd)