SQL Server – Sp_refreshview for all views in a Database

Run in the context of each database as a nightly or weekly job depending on how often your non-schemabound views and underlying tables are altered.

DECLARE @viewname NVARCHAR(255)

DECLARE @looper INT = 1

IF OBJECT_ID('tempdb..#viewnames') IS NOT NULL

BEGIN

DROP TABLE #viewnames

END

SELECT

    s.[name] + '.' + v.[name] vname,

    ID = ROW_NUMBER() OVER (PARTITION BY v.[type_desc] ORDER BY v.[name])

INTO #viewnames

FROM sys.views v

JOIN sys.schemas s ON v.schema_id = s.schema_id

WHERE OBJECTPROPERTY(OBJECT_ID, 'IsSchemaBound') = 0

WHILE @looper <= (SELECT COUNT(*) FROM #viewnames)

BEGIN

SET @viewname = (SELECT vname FROM #viewnames WHERE ID = @looper)

EXEC SP_REFRESHVIEW @viewname

PRINT 'Exec sp_refreshview ''' + @viewname + ''''

SET @looper += 1

END