Wednesday, June 11, 2014

views structure is not refreshing


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

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate t...

Contributors