Have you ever downloaded a script from an internet site, only to find that there are extra blank lines between all the lines of code? Or perhaps you have results that you need to extract information from in SSMS, but the data isn’t in a clean format? Do you go through and change things line by agonizing line? Of course not. You use the built in regular expressions capability of SSMS.
In our last tip, we use registered servers to house a list of SQL Server instances. Let’s say that some of them have been retired. When you run a multi-server query against them, those will return an error. Is the server just down? Or should you remove it from the registered servers list? You need a list of the servers to investigate further.
In this scenario, I have the following servers registered. Only the first is active:
When I run a query, I get this error message for the missing instances:
|
1 2 |
oldserver1(WS-M6700\wgshef): An error occurred while executing batch. Error message is: The server is not connected. oldserver2\inst1(WS-M6700\wgshef): An error occurred while executing batch. Error message is: The server is not connected. |
I can copy this output to a new query window and strip out everything except the server\instance names. In this case, I want to remove everything after the first opening parenthesis to the end of the line.
The Regular Expressions are accessed from the Find / Replace dialogs. Depending on the version of SSMS used, there are different ways to access the Regular Expression. On older versions of SSMS (through SQL Server 2014), you expand the options:
Then check “Use” and select “Regular Expressions”:
On newer versions of SSMS (2016+), the third icon along the bottom toggles Regular Expressions:
On the older versions, that little triangle beside the Find / Replace boxes will show you the regular expressions that you can use:
In the scenario I’ve given, there are two possibilities that we can use:
[(].*$
\(.*$
One of the things that this screen doesn’t show is that parentheses define a set, so these methods here specify to use the character in brackets, or to escape the open parenthesis. Use either of these in the find dialog, and in the replace make it an empty string.
In newer versions of SSMS, this will even highlight the areas about to be replace:
When you replace all, only the server\instance names will be left:
MCM/MVP Sean McCown has done several presentations devoted to using RegEx in SSMS. He has recorded several of his presentations, and they are available at his blog.
This post is for day ten of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.
