Making changes to multiple Stored Procedures

[rdemotsis]

I have been given the task of updating hundreds of stored procedures in many different databases with our organizations. The first name field and the last name field have been expanded to a varchar(50) in the tables and the sp's have varchar(20). Is there any way to create a script that will do a search and replace for me.

I've got blisters on my fingers.......

 

[nickb]

I have not tried this but it appears to me the possible solution is to generate an SQL script for the database selecting only stored procedures. (It is found under the database name with a right mouse button under the menu tasks)
Save the results in a file and open with a text editor using the find and replace commands. Then run the script to rebuild your stored procedures.

Please as I stated before. I have not tested this , So please test before using on live system.


Regards

Nick Bailey

 

[MalcolmW]

Nick
I use that approach to send sp by email - the SQL script is a useful way of doing that.

Seems to me it is possible to search and replace the sp text for different databases from system database, and I had read about a sp to do that in the SQL Server 7 text I read. Sorry for the useless and vague response, but I do think it is possible - perhaps someone else (with more brain cells) can help. Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[Guest_imported]

Hi.

There's products just for managing Stored Procedures.

Check SQL-Programmer at:

http://www.sfi-software.com/sql-programmer.htm

The "search" also allows to search within database server Stored Procedures, Triggers, Tables, etc...

FREE Evaluation copies of SQL-Programmer 2001 at:

http://www.sfi-software.com/request/downloadrequest.asp

I'm sure this will help.

Sylvain Faust

 

[TomSark]

I have used SQL Programmer and think it's a great tool. I would only buy it if I had to manage more than SQL Server objects, however, (like Sybase, Oracle, SQL Server).

If this is the only reason why you would need it, I would write a script to do this instead of buying the tool.

Again, I like the tool, but would buy it for other reasons, such as the cool stored procedure debugger it has...

SQL Server 2000's query analyzer will let you search for objects as well, but I don't recall a search and replace feature...

Tom

 

[rdemotsis]

Thanks to all for the replies. The SQL Programmer tool seems pretty nice. I will further evaluate it and also look in to creating my own procedure for conducting a search and replace method.