stored procedure text string replace

• sqlfriend

  SSC Guru

  Points: 52794

  More actions

  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

• x

  SSC-Insane

  Points: 23660

  More actions

  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

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  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