How can I update text within view then update view in script?

Question

Using SQL Server 2008 R2. I need to write a SQL script that will search for specific text (e.g. server name) in existing SQL views, replace this text with another string, then update the view (ALTER VIEW). I can't figure out how I would write this script. Anyone do something similar they can share?

Would I extract the entire text into a variable, perform REPLACE, then "rewrite" the entire view from this variable?

I am able to alter the view statement, not sure how best to get the view refreshed with this @viewtext variable:

declare @viewtext nvarchar(max)

set @viewtext = (select definition from sys.objects so
    join sys.sql_modules sm on sm.object_id = so.object_id
where so.type = 'V'
    and so.object_id = object_id('dbo.vwHistoryByLocation'))

set @viewtext = (select replace(@viewtext,'.PROD.','.TEST.'))
set @viewtext = (select replace(@viewtext,'CREATE VIEW','ALTER VIEW'))
select @viewtext

Not sure if there's a better way or not, but got back to working on this and got it to work as follows:

declare @rc int = 0
declare @vwtext nvarchar(max)
declare @name nvarchar(max)
declare @descr nvarchar(max)

declare cur cursor for
    select so.name, sm.definition from sys.objects so
        join sys.sql_modules sm on sm.object_id = so.object_id
    where so.type = 'V'
        and sm.definition like '%.PROD.%';
open cur;
fetch next from cur into @name, @descr
while (@@FETCH_STATUS = 0)
    begin
    set @vwtext = (select replace(@descr,'.PROD.','.TEST.'))
    set @vwtext = (select replace(@vwtext,'CREATE VIEW ','ALTER VIEW '))
    select @vwtext

    exec @rc = sys.sp_executesql @vwtext

    fetch next from cur into @name, @descr;
    end
close cur;
deallocate cur;

Answer 1

I found the solution. The above description I entered has resolved my problem, and in fact I was able to push this routine into a stored procedure so I can pass the source and target text values for whatever variables I need to update. Thanks AMA for your contribution.

Answer 2

Is your view updatable? If so, you may consider using:

UPDATE < view_name > SET<column1>=<value1>,<column2>=<value2>,.....
WHERE <condition>;

A view is updatable if these conditions are met:

1. The view is defined based on one and only one table.

2. The view must include the PRIMARY KEY of the table based upon which the view has been created.

3. The view should not have any field made out of aggregate functions.

4. The view must not have any DISTINCT clause in it's definition.

5. The view must not have any GROUP BY or HAVING clause in it's definition.

6. The view must not have any SUBQUERIES in it's definitions.

7. If the view you want to update is based upon another view, the later should be updatable.

http://www.w3resource.com/sql/update-views/sql-update-views.php#sthash.J5yJBTS8.dpuf