Sql script to REFRESH all views in a SQL Server Database

With the help of below sql script we will refresh the all views in all databases in sql server.

DECLARE @DDL VARCHAR(MAX) DECLARE @ViewName VARCHAR(100) DECLARE @DatabaseName AS VARCHAR(500) --DECLARE CURSOR DECLARE CUR CURSOR FOR --Choose the DBs on which we would like to run the script SELECT NAME FROM sys.databases WHERE database_id > 4 --OPEN CURSOR OPEN CUR --NEXT NEXT RECORD FETCH NEXT FROM CUR INTO @DatabaseName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @DBName AS NVARCHAR(500); SET @DBName = QUOTENAME(N'' + @DatabaseName + ''); EXEC ( N'USE ' + @DBName +''); ----Cursor to list of view in database                 DECLARE CUR_View CURSOR                 FOR SELECT NAME FROM sys.views                 OPEN CUR_View                 FETCH NEXT FROM CUR_View INTO @ViewName                 WHILE @@FETCH_STATUS = 0                 BEGIN                 SET @DDL = 'sp_refreshview  '+ @ViewName                 EXEC(@DDL);                 print 'Database name : ' +@DatabaseName +' View name : '+ @ViewName +' Refreshed on :' +                 cast(Getdate() as varchar(100))                 FETCH NEXT                 FROM CUR     INTO @DatabaseName                 END                 CLOSE CUR_View                 DEALLOCATE CUR_View --USE Dynamic SQL To Change DB name and run DDL statement to Drop table FETCH NEXT FROM CUR INTO @DatabaseName END CLOSE CUR DEALLOCATE CUR

See the output.

All views are refreshed.