Post reply

How to search and replace multiple stored procedures?

  • October 15, 2007 at 9:44 am

    #182846

    We are promoting a lot of old stored procedures to production but the are referencing a development box. Is there any easy way to search all the procedures and update them? I tried updateing the defintion field in the sys.sql_modules, and also tried updating the syscomments.text but was unable to. Here is an example of what I am trying to do.

    in a stored proc we have the following select * from vmdproduction.test.dbo.table1, I would like to rename the vmdproduction to production in all cases across all stored procedures.

    Thanks

  • October 15, 2007 at 9:47 am

    #742030

    Not sure about the replacing, but SQL Digger is pretty handy and will find all of the objects that need modified.

    http://sqldigger.bdsweb.be/

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • October 15, 2007 at 9:58 am

    #742037

    Thanks,

    But I think that is more for searching where a word or phrase is being used.

  • October 15, 2007 at 10:05 am

    #742043

    here's something that colin wrote to search the text of all objects.

    because a procedure is compiled, changing the text of a proc would have no effect...you must alter the procedure to recreate it.

    this should help you find what you are looking for, however:

    Create procedure sp_FindText

    @wot varchar(250)=' ',

    @wot2 varchar(250)=' '

    -- ============================================================================

    -- Stored Procedure: sp_FindText

    --

    -- Written by: Colin Leversuch-Roberts

    -- kelem consulting limited

    -- http://www.kelemconsulting.co.uk

    --

    -- Purpose: Search for system objects containing the passed string(s)

    -- These are wild card searches

    -- Returns the object code and type of object

    --

    -- System: master database

    -- does not need to be marked as a system object

    --

    -- Input Paramters: @wot varchar Search string

    -- @wot2 varchar Search string

    --

    -- Output Parameters: None

    --

    -- Usage: Call from user database to be searched

    -- EXEC dbo.sp_findtext 'tbl_sales'

    -- EXEC dbo.sp_findtext 'aug','uat'

    --

    -- Calls: nothing

    -- Uses: syscomments, sysobjects

    --

    -- Data Modifications: None

    --

    -- VERSION HISTORY

    -- Version No Date Description

    -- 1 22-August-2004 Initial Release

    -- ============================================================================

    as

    set nocount on

    --

    select obj.name,obj.xtype,

    case obj.xtype

    when 'TR' then 'Trigger'

    when 'P' then 'Procedure'

    when 'V' then 'View'

    when 'TF' then 'Function'

    when 'IF' then 'Function'

    when 'FN' then 'Function'

    else 'Unknown'

    end

    ,c.text

    from dbo.syscomments c join dbo.sysobjects obj

    on obj.id=c.id

    where

    patindex('%'+@wot+'%',text)<>0 and patindex('%'+@wot2+'%',text)<>0

    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!

  • October 15, 2007 at 10:06 am

    #742045

    Right, you search for "vmdproduction" and it will give you a list of all objects that have the server name hard-coded. You can then copy the SQL from the results window, do an Edit and Replace in SSMS and run the code.

    Again, this will at least help identify the areas that need changed.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • October 15, 2007 at 10:18 am

    #742048

    I am not sure if I understood this correctly, but are you looking for something like this?

    SELECT REPLACE(object_definition(object_id(name)),'SERVERNAME','NEWSERVERNAME') FROM sys.objects WHERE type = 'P'

    You could also replace the "CREATE" at the beginning of the proc with "ALTER" if needed.

    Best Regards,

    Chris Büttner

  • October 15, 2007 at 10:25 am

    #742050

    Christian now THAT was sweet idea; i forgot about the object_defintion function in 2005; only thing i did for testing was to add a WHERE statment to make sure i got only affected procs, and not all procs:

    SELECT REPLACE(object_definition(object_id(name)),'OLDSERVER','NEWSERVER') FROM sys.objects WHERE type = 'P'

    and charindex('OLDSERVER',object_definition(object_id(name))) > 0

    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!

  • October 17, 2007 at 8:44 am

    #742754

    Not sure if you've resolved your issue or not, but this might be the right tool for you:

    http://www.toadsoft.com/toadsqlserver/toad_sqlserver.htm

    It's a free tool called TOADSoft for SQL Server, and apparently it can "auto-relace SQL".

    I was checking it out for another reason, and thought it might be handy in your situation.

    -Simon

  • February 13, 2010 at 12:12 am

    #1118397

    no that is just autocorrect within the query editor

  • August 31, 2015 at 10:23 pm

    #1823541

    Just in case anyone arrives here from a search engine, the two places you're looking for are sys.sql_modules and information_schema.routines, although as Aaron Bertrand pointed out here (The case against INFORMATION_SCHEMA views), Microsoft have been steering people away from it for a while.

    You can't update those views though (SQL Server doesn't allow you to do it), so go to this post: http://www.sqlservercentral.com/Forums/Topic907448-146-1.aspx

  • August 31, 2015 at 10:48 pm

    #1823543

    Just in case anyone arrives here from a search engine, the two places you're looking for are sys.sql_modules and information_schema.routines, although as Aaron Bertrand pointed out here (The case against INFORMATION_SCHEMA views), Microsoft have been steering people away from it for a while.

    You can't update those views though, so your best option is to attempt the solution here: http://www.sqlservercentral.com/Forums/Topic907448-146-1.aspx

  • September 1, 2015 at 10:24 am

    #1823685

    One addition to the really old post.

    Load your database into a Database Project in Visual Studio (SSDT - SQL Server Data Tools free add-in for VS). Then you can search/replace like any other VS project. Then create a publish script against your database and it will create all of the necessary scripts to convert everything over.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

Viewing 12 posts - 1 through 11 (of 11 total)

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