1

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?

  • 2
    Just a side note here. The same principles that apply to software development also apply here. The source code of the views (and procedures, functions, etc.) should be in a repository outside of the database. The code fix can be applied, a new build generated, tested, deployed. Correct your views with whatever editor you prefer, and build a deployment script that can be run on each target database as necessary. Version control is critical, as is database/application versioning. – datagod Sep 27 '16 at 19:43
  • That is good to know. Unfortunately right now that doesn't exist outside of a backup, and everything is (apparently, I don't believe this but its what I'm told) deployed by hand to the servers. – Adam Wells Sep 27 '16 at 19:46
  • 3
    What happens if a database is restored? The old code will be restored as well. Not much you can do for the existing project. Learn from the pain it will create for you. Let that pain burn, it is a good teacher. On the next project demand better. Treat database code just like any other, and look at the databases as the compiled object. People don't crack open applications out in the field and replace libraries or chunks of binary (usually!), so why is it acceptable to do that do a database? – datagod Sep 27 '16 at 19:54
  • Ah, yes, their favorite term "We've always done it this way" – Adam Wells Sep 27 '16 at 20:01
0

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
  • I'll give it a spin and let you know, though I'd be interested in the PowerShell solution as well, more for academic purposes than anything. – Adam Wells Sep 28 '16 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.