T-SQL script to REFRESH all views in a SQL Server Database

6
70

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

6 COMMENTS

  1. 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

two × 2 =