updating a variable in stored procedure using query

Question

Hi There I have a requirement where i have to update all stored procedures in my server which has a constant variable i have to updated all my stored procedures based on that constant variable using a single query/stored procedure. can any one please anyone help me with this requirement.

Pankaj Pandey · Answer

hello try below query select replace(replace(definition'create''alter')'x''y') from sys.sql_modules where definition like'% procedure %' or definition like'% proc %' it will show a list of definition of all stored procedures. Copy all rows and paste on query editor and click on run it will alter all your procedures in one shot.

midhun b · Answer

THANK U VERY MUCH FOR THE REPLY PANKAJ PANDEY IT WORKED AND HELP ME A LOT. THANKS ONCE AGAIN

midhun b · Answer

for example i have 10 Stored procedures and i have a static variable say x. now i want to replace that x in all my stored procedures with say y. with a single query / stored procedure. this is what my requirement

Pankaj Pandey · Answer

hello Can you explain your need with example i had worked with dynamically creation of SPs so may be i will help you but after getting completely what your need...so explain it with an example.