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.