If the underlying structure of the view changes , then the views which are referring these objects wont refresh always with the new structure.
Below script will loop through all the views and executes the sp_refreshview on each view.
Note : used the implicit transaction , else if any statement fails then the rest of the execute statements in this loop wont execute though its using Begin Try and Catch.
create proc refresh_views as set nocount on set xact_abort on declare @vwname varchar(256) declare @err varchar(max) = '' declare cur cursor for select TABLE_SCHEMA+'.'+TABLE_NAME FROM INFORMATION_SCHEMA.views open cur fetch next from cur into @vwname while @@FETCH_STATUS = 0 begin begin try -- It has to be in a transaction , else if any statement fails then the rest of the execute statements in this loop wont execute though its using Begin Try and Catch BEGIN TRAN EXECUTE sp_refreshview @vwname COMMIT TRAN end try begin catch ROLLBACK TRAN set @err = @err +','+@vwname+':'+ERROR_MESSAGE() end catch fetch next from cur into @vwname continue end close cur deallocate cur if @err <> '' begin select 'Sync Has the errors while refreshing the follwing views '+@err end |
No comments:
Post a Comment