Refresh all Views

Query to 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 the IBIS / Socius 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 'vw_dk%'
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)