loop through all stored procedures in database and modify.

I have a need to loop through all stored procedures within a database and modify the procedure if it meets certain criteria. Using .NET I was able to develop a project to loop through the stored procedures, but that's as far as I got. What I need to do is open the stored procedure, search for a certain text string, and change it. Does anybody have a suggestion as to how I can accomplish this? Thanks.

Karen

create the script of all the sp to a single file (which is possible in EM or SSMS) . then replace the string by FIND & REPLACE method .. then run the script in the database, remember to script the permission too...

Madhu

|||

Hmm.

I have two servers with about 100 databases total. Each database uses a single stored procedure that may be different from the ones in the other databases. I wanted to be able to open the stored procedure, search for the string, then modify the text and save the script in an automated way. And I was wondering if SQLDMO can be used through .NET.

|||

You can get the script of this storedprocedure to a file using SQLCMD. but replacing the text u will have to do by .NET program.

read about SQLCMD

SQLCMD -q"SP_HelpText 'Your SP Name'" -o "Outputfile.sql"

Madhu

|||If you are using SQL Server 2000, all of this is available via SQLDMO. You can get a collection of the databases, loop across all of the databases, get a list of procedures, loop across the procedures and extract the definition, then use the string functions in .NET to find and replace. If this is SQL Server 2005, the same capabilities are available with SMO. The samples that ship with SQL Server pretty much have all of the code that you would need.