Change a specific line of a view with conditions

Question

I've recently started working at a company that maintains between four hundred and five hundred client databases, and am doing various maintenance tasks on code (I am junior level after all). I've been tasked with identifying a particular bug in a group of views across the entire scope of the company, and changing each one. I was able to identify all of the culprits already via SQLSearch (a lifesaver), but am now tasked with modifying one particular line of code to a new standard across almost 1,200 views.

I know:

• The wrong line of code (there are 3 variations, i know all three)
• The correct line of code I need to replace the wrong lines with
• Which views and which databases have the error.
• How many views in each database are currently 'wrong'

How can I easily modify the views to correct the error without doing it by hand?

Answer 1

This is very easy to achieve with PowerShell (in which you can use Regular Expression to do your work) But here for simplicity purpose, I will use a t-sql solution with a simplified example as shown below

-- assume you have a view called dbo.v and you want to replace one line in this view
use tempdb
go
drop view dbo.v
go
create view dbo.v
as
    select A = 'test 1'
    union all
    select A = 'test 2' -- we will change this to 'test 222'
    union all
    select A = 'test 3'
go
-- assume we will replace the line select A = 'test 2'
declare @sql varchar(max);
declare @original_string varchar(300) = 'select A = ''test 2''';
declare @replace_string varchar(300) = 'select A = ''test 222''';


select  @sql=definition from sys.sql_modules
where object_id = object_id('dbo.v')


set @sql = replace(@sql, @original_string, @replace_string);
set @sql = replace(@sql, 'create view', 'alter view');
--print @sql -- uncomment it if needed

exec (@sql)
go