TSQL script to repoint all views for new linked server

Use the following TSQL cursor script to repoint views in bulk towards a newly created linked server

DECLARE @v_sql VARCHAR(max)

DECLARE @vnum int

set @vnum 1

DECLARE db_cursor CURSOR FOR 

SELECT

REPLACE(

        REPLACE(definition,'CREATE VIEW','ALTER VIEW'),

        'INSIGHT.','REPORT_INSIGHT.'

        ) 

from sys.sql_modules

where definition like '%INSIGHT.%'

and definition like '%VIEW%'

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @v_sql  

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print @vnum

       exec(@v_sql)

       set @vnum =  @vnum + 1

       FETCH NEXT FROM db_cursor INTO @v_sql  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor