Refresh all views in database

If there are views created without SCHEMABINDING then persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends. To update the view to the latest metadata it can be recreated or refreshed using the sp_refreshview procedure. Because views and its metadata can be nested refresh must be executed for all levels.

The following script will search for all views in the database and perform a call of sp_refreshview for each of them. Please adjust the command execution to your nesting level. Currently, it is set to three levels.

DECLARE @Stmt NVARCHAR(MAX)
 
SET @Stmt = ''
 
SELECT  @Stmt = @Stmt + 'EXEC sp_refreshview ''[' + [s].[name] + '].[' + [v].[name] + ']'';' + CHAR(13) 
FROM [sys].[views] [v]
	INNER JOIN [sys].[schemas] [s] ON [s].[schema_id] = [v].[schema_id]
ORDER BY [s].[name], [v].[name]

PRINT @Stmt

-- Copy as needed to cover lowest nested level
EXECUTE (@Stmt)
EXECUTE (@Stmt)
EXECUTE (@Stmt)
GO
 
-- Check modify_date that it's updated
SELECT [s].[name] [SchemaName], [v].[name] [ViewName], [v].[create_date], [v].[modify_date]
FROM [sys].[views] [v]
	INNER JOIN [sys].[schemas] [s] ON [s].[schema_id] = [v].[schema_id]
ORDER BY [s].[name], [v].[name]
GO