Post reply

Update Stored procedure text

  • March 10, 2003 at 6:53 pm

    #81139

    I've got 250+ procedures that need one line of text changed. Can anyone point me in the right direction to update the text of all these stored procedures?

    Here's what I've knocked up so far. At the moment this incorrectly changes the stored procedure text to 'b' 

    

    update dbo.syscomments 

    set ctext = CONVERT ( varbinary(8000) , 

    Replace ( convert(nvarchar(4000) ,

    case when (status & 2 = 2) then (uncompress(ctext)) else ctext end )

    , 'old text', 'new text' ) )

    Thanks

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    Edited by - phillcart on 03/10/2003 6:54:13 PM

    --------------------
    Colt 45 - the original point and click interface

  • March 11, 2003 at 2:31 am

    #450764

    I always script my procs and therefore change the text files and update DB. In your case I would use EM to script all the procs into a single file, edit the text file (replace all) and run the script through ISQL.

    Far away is close at hand in the images of elsewhere.
    Anon.

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

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