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