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
