Post reply

stored procedure text string replace

  • April 16, 2015 at 10:05 am

    #296134

    We have a legacy database that have hundreds of stored procedures.

    The previous programmar uses a string like servername.databasename.dbo.tablename in the stored procedures.

    We now have migrated the database to a new server. The old server is either needed to be replaced by the new server name, or remove it.

    I don't know why he used servername as part of the fully qualified name, we don't use linked servers. So I think better removing the servername in all the stored procedures.

    I know I can do a generate script, and replace the text and then use alter procedure to recreate all the stored procedures. But since hundreds of them, is there a programmatically way to replace them?

    Thanks

  • April 16, 2015 at 10:32 am

    #1791424

    some pretty good discussion here:

    http://stackoverflow.com/questions/10269172/quickest-easiest-way-to-use-search-replace-through-all-stored-procedures

  • April 16, 2015 at 10:48 am

    #1791432

    You can get the schema, name and DDL for the stored procs from INFORMATION_SCHEMA.ROUTINES. Something like this...

    DECLARE @buildSprocsScript nvarchar(max) ='';

    SELECT @buildSprocsScript += 'DROP PROC '+specific_schema+'.'+specific_name+char(13)+'GO'+char(13)

    +REPLACE(ROUTINE_DEFINITION,'<what you want to replace>','<replace with>')+' GO'+char(13)

    FROM INFORMATION_SCHEMA.ROUTINES

    SELECT @buildSprocsScript;

    EXEC(@buildSprocsScript)

    This is not a complete solution and would need to be updated to suit your needs but should give you an idea of how to move forward. It goes without saying: test, test, test.... I would try this out on a dummy box a few times to look for any gotcha's.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 3 posts - 1 through 3 (of 3 total)

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