Jul 282020
 

In my last Management Studio tip, I demonstrated how you can drag and drop column names to a query window. The only part I do not like about that is that all the column names are on a single line. I prefer to have on column name per line, and to quickly fix this we are going to use a Regular Expression (RegEx) within the Find and Replace dialog box.

For those not familiar with RegEx, it allows you to search through text using a multitude of options. In our example, we are going to use RegEx to quickly find and replace text in a script.

Below we have a single select statement that has all column names on a single line. We want to get each column name on a separate line and with the comma at the beginning of the column name.

Press Ctrl+H to open the Find and Replace dialog box. Then we need to click the RegEx icon.

In the Find box we want to find the comma and the space that follows it: “, “.

In the Replace box we want to enter “\r\t,”.

\r means we want to insert a carriage return.

\t means we want to insert a tab.

And follow that with a comma. Click the Replace All button.

Now we should have each column name on a separate line.

Next, we will use RegEx to get rid of the square brackets around the column names.

In the Find box we want to enter “\[|\]”.

\[ means we want to look for the left square bracket. The backslash is used to escape the left bracket since it is a RegEx special character.

| (vertical pipe) is used for an OR statement (i.e. we want to find either the left or right bracket).

\] means we want to look for the right square bracket. Once again, the backslash is used to escape the special character.

We want to leave the Replace box empty. Click the Replace All button.

Now we should see that our script is formatted in a way that is much easier to read.

These were simple examples using a small script, but RegEx can be an incredibly powerful when you need to search (and replace) text in a script that may be thousands of lines long.

Additional Reference:
https://docs.microsoft.com/en-us/sql/ssms/scripting/search-text-with-regular-expressions?view=sql-server-ver15

Jul 072020
 

Continuing on my recent trend of Management Studio tips, here is another that could save you a few minutes.

In today’s scenario, we need to write a select statement that returns only the first few columns of a table. It is a super simple statement, but instead of typing out all the column names, we can just drag and drop them from the Object Explorer.

Watch the video below for a quick demo.

 

If you are not a fan of the square brackets around each object name, then you can change the settings so they no longer appear.

From the menu, select Tools and then Options.

Select the SQL Server Object Explorer tab and then change the “Surround object names with brackets when dragged” to false. Click OK to save the changes.

It takes effect once you open a new query window.

 

Note: This setting only works for drag and drop. Generated scripts will still use the square brackets.