Thursday, July 26, 2012

Neat SSMS trick

One of the developers asked me this morning if there was a way to do a replacement in Management Studio where you look for a constant string at the beginning of the line, followed by arbitrary stuff, and essentially append something else to the end of it. The use case was he had a stored procedure that was called in a bunch of places with different parameter orders that he needed to add a couple of more parameters to. Both he and I knew that SSMS has support for regular expressions, but didn't immediately know how to pull this off. Surprisingly, it's pretty easy. In the "Find what:" box, you put in something like "exec sp_myProc{.*}" and in the "Replace with:" box you put in "exec sp_myProc\1 @newParm1='blue', @newParm2='fragrant'". The "\1" refers to the first capture group within the regular expression. I found the regex reference for Visual Studio to be most instructive. You can find that here. Have fun!

No comments:

Post a Comment