September 3, 2008 at 8:27 am
This is wtihin Microsoft SQL Server Management Studio (v9.00.3042.00), targeting a SQL Server 2000 database.
I have a number of stored procedures that I want to replace text within. Unless there's an easier way, I was thinking I could generate ALTER scripts for all of them, do a big find and replace on the text I need changed, and then run the whole thing, altering them all.
I am viewing my list of stored procedures in the Object Explorer Details screen. I can select multiple stored procedures by clicking on them and holding down the shift or control keys. After I have selected multiple stored procs, I click the right mouse button to bring up the context menu.
I do "Script Stored Procedure As"-> and can only select the CREATE or DROP options; the ALTER option is unavailable. Why?? Why can't I alter multiple scripts at once?
Is there an easier way to do what I want rather than individually modify every single one, finding and replacing and executing? Thanks!
September 3, 2008 at 9:33 am
Could you just generate the 'Create' scripts, and then do a find replace of 'Create' with 'Alter'...
September 3, 2008 at 9:40 am
k man (9/3/2008)
Could you just generate the 'Create' scripts, and then do a find replace of 'Create' with 'Alter'...
Hmmmm....it does seem like that would work; I wasn't sure if the syntax was the same or not.
Weird...why can I do the CREATE generation but not the ALTER one? Bug or feature?
September 3, 2008 at 9:59 am
ALTER is not an option for the Script Generator. The Script Generator is the thing in SSMS that can script multiple objects at a time.
The facility that creates the scripts of individual objects ("scripter"?) has more type specific features, including "ALTER" for most everything except tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 10:06 am
Not a required feature. The fact that you need to alter the procedure suggests that you will manually (99% of the time) will change the code.
That feature has been implemented that way. You can right-click on a procedure and edit it. As far as I know, you can't do that process in batch unless you code it yourself (like printing all the code, copy / paste into query editor, search / replace and reexecute).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy