Adjusting the directory declared within stored procedure

Question

There are multiple stored procedures where a variable lets say

@filepath = '//c:/users/public/adp_file/processed' 

is declared. Now I need to replace only specific portion of the directory path such as c:/user/public with //nclas/localnca/ and get result as

@filepath = '//nclas/localnca/adp_file/processed' 

for all the stored procedure within different databases.

FYI: there are many databases having such stored procedure where @filepath is declared with this c:/user/public portion in the directory path.

At present I am manually doing by using find and replace option for every stored procedure one by one. I was wondering is there any efficient way to get it done.

Answer 1

You can do database by database: by scripting the stored procedures (all stored procs in the database) to files, then use Replace in files (Notepad++ or VisualStudio, and many others), and then run those files.