.


/*This SQL script will refresh all views in the database, regardless of the
schema. A cursor is created first, then the script loops through the list
of the views. sp_refreshview is called for each view inside the loop.*/
ALTER PROCEDURE [dbo].[SP_REFRESH_ALL_VIEWS_IN_DATABASE]
AS
SET NOCOUNT OFF
DECLARE @ActualView varchar(255)
DECLARE viewlist CURSOR FAST_FORWARD
FOR
SELECT
   s.name + '.' + o.name AS ViewName
FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE  o.[type] = 'V'
       AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1
       AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1
ORDER BY s.name, o.name
OPEN viewlist
FETCH NEXT FROM viewlist
INTO @ActualView
Declare @objectCount int =0
--Print '======================================================='
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @objectCount=@objectCount+1
  --Print @ActualView +'- Processing ...'
  EXEC sp_refreshview @ActualView
  --Print @ActualView + '- Refreshed'
  --Print '--------------------------------------------'
  FETCH NEXT FROM viewlist
  INTO @ActualView
END
--Print '======================================================='
--Print CAST(@objectCount as varchar(255) )+' Views refreshed.'
CLOSE viewlist
DEALLOCATE viewlist
GO
EXEC SP_REFRESH_ALL_VIEWS_IN_DATABASE