Post reply

replace text in large number of view and procedure definitions

  • September 18, 2017 at 11:19 am

    #332715

    I want to find and replace one particular word (happens to be the name of a database, which I know exists in a given list of objects, probably in a USE DATABASE statement), and replace it with another word.

    I have the list of object names (procedures, scalar functions, and views) wherein I want to find the word (WORD1) and replace it with another word (WORD2).

    Is there an automated way to do this?

  • September 18, 2017 at 11:40 am

    #1959851

    I am always so afraid of using a shot gun approach without definitive testing and eyeballing for correctness, because a find/replace could nail partial words /strings.
    I would use either SSMS to script out all objects, or a PowerShell script to script all database objects to a file, and open them in a proper text editor or SSMS, and do the find replace, and execute them on Dev, and see if they compile.

    you have to do multiple searches, because ObjectName and [ObjectName], when matching for Whole words, are two different results to avoid accidentally finding PartialObjectName, as a match that should not really be a match.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 18, 2017 at 11:52 am

    #1959852

    pisorsisaac - Monday, September 18, 2017 11:19 AM

    I want to find and replace one particular word (happens to be the name of a database, which I know exists in a given list of objects, probably in a USE DATABASE statement), and replace it with another word.

    I have the list of object names (procedures, scalar functions, and views) wherein I want to find the word (WORD1) and replace it with another word (WORD2).

    Is there an automated way to do this?

    USE DATABASE in your db code? I hope you are mistaken, as this is not good practice.

    The way that I accomplish such tasks is probably less automated than you might like. But it is fairly robust, safe and repeatable.

    1) Extract your database into a VS database project
    2) Put the database project under source control & check it in 
    (You probably have this in place already)
    3) Use your favourite file utility to perform the Find/Replace.
    (I wrote my own)
    4) Use your version control software to check that the Find/Replace worked as intended
    (Easy to undo all changes and try again if it did not)
    5) Deploy to database from local .SQL files.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • September 18, 2017 at 12:19 pm

    #1959856

    Thanks for the advice, gentlemen, appreciated.
    I've definitely never heard the comment about not to use USE DATABASE code before, so...will have to read up on that.  Thanks ...........

  • September 18, 2017 at 12:43 pm

    #1959858

    pisorsisaac - Monday, September 18, 2017 12:19 PM

    Thanks for the advice, gentlemen, appreciated.
    I've definitely never heard the comment about not to use USE DATABASE code before, so...will have to read up on that.  Thanks ...........

    One day, a helpful DBA restored a copy of a production database X to X_Test to investigate some issues.

    During the investigation, the DBA ran a proc inside of X_Test. Unfortunately, this proc contained hard-coded updates to production database Y, and the DBA soon found himself seeking alternative employment.

    In an ideal world, databases do not have any dependencies on other databases to avoid this potential problem.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • September 21, 2017 at 2:43 pm

    #1960423

    Okay, thanks for the illustration.  I mean ... I could have guessed this, but it just seems like one of a number of possible things, if people are going to hit F5 without thinking at all, then there is no limit to the number of possible "are you sure you meant to do this?" scenarios.  To me this seems like one of them .. a little bit at least?  
    But I totally see what you mean.

    At first I thought you meant USE DATABASE as opposed to just [DatabaseNamePrefix].Schema.Table ...............  etc. etc.
    But you're referring to the practice of specifying the database in the first place. 

    Hmm... Ok, I'll give this some thought.  I mean obviously I can see the value in it.  Thanks

  • September 26, 2017 at 9:16 am

    #1960980

    Phil Parkin - Monday, September 18, 2017 12:43 PM

    pisorsisaac - Monday, September 18, 2017 12:19 PM

    Thanks for the advice, gentlemen, appreciated.
    I've definitely never heard the comment about not to use USE DATABASE code before, so...will have to read up on that.  Thanks ...........

    One day, a helpful DBA restored a copy of a production database X to X_Test to investigate some issues.

    During the investigation, the DBA ran a proc inside of X_Test. Unfortunately, this proc contained hard-coded updates to production database Y, and the DBA soon found himself seeking alternative employment.

    In an ideal world, databases do not have any dependencies on other databases to avoid this potential problem.

    Ran into this one before.....gave me a headache....

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply