T-SQL script to refresh all VIEWS in DATABASE.
declare c cursor for
select name from sysobjects where type = 'v' and uid = 1
open c
declare @ViewName varchar(500)
fetch next from c into @ViewName
while @@fetch_status = 0
begin
BEGIN TRY
exec sp_refreshView @viewName
END TRY
BEGIN CATCH
print @viewName
END CATCH
fetch next from c into @viewName
end
close c
deallocate c
getting following error during execution of your code….
Msg 266, Level 16, State 2, Procedure Refresh_View, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
Even not able to execute the same with sp_msforeachdb….
SQL user needs to have proper permissions to execute these procedures, this approach looks better as we can even filter out any objects in the SELECT statement as per our need.
I rechecked, it works all fine.
Does this refresh even the encrypted views?
Yes, it will work with encrypted views also.
Thanks.