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.

 

Jun 232020
 

Have you ever had a need to edit multiple lines in a T-SQL script? As in, type the same text repeatedly on multiple lines? Well, a little-known key stroke makes this an easy task in Management Studio.

Simply place your cursor where you want to start typing, hold down Alt+Shift and then hit either the Up or Down arrow key. You will then see a vertical blinking cursor. Now just start typing. You can even hit Enter which will add a new line in between each one where you can continue typing.

Watch the video below for a quick demo.

May 282019
 

The other day I was asked why Management Studio limits you to only 200 rows when you right click on a table to edit the data.

The easy answer is because that is how Management Studio is configured. However, it’s also something that can easily be changed. From the menu just select Tools and then Options.

In the Options window, select SQL Server Object Explorer on the left side. On the right side you’ll see the the Table and View Options. Change the Value for Edit Top <n> Rows to something like 500 and then click OK.

Now go back and right click on your table. You will now see Edit Top 500 Rows.

An alternative to changing the configuration value is to click the SQL button on the toolbar.

This opens a query window which will then allow you to edit the TOP (n) value. Once you change the value, then click the Execute button on the tool bar.

You can also rewrite the query to filter out more of the rows before you edit them. Just follow the same steps above; edit the query and then click the Execute button on the toolbar.

Finally, in the same Options window, you can change the value for the Select Top <n> Rows.

This will change the same right click menu except for the the select.

 

May 142019
 

There is still no love for SQL Server Management Studio. SSMS v18 Beta included a Dark Theme, but somehow that did not make it into the final release of the product. Other apps have this capability (Visual Studio, Visual Studio Code, Chrome, Edge) but not SSMS. If you are like me, sometimes you want to switch your apps to a dark theme for added flair. A quick web search will reveal several ways to hack SSMS to display a dark theme, but that may not produce the best results. So, until Microsoft decides to include it, here is how I have been customizing SSMS for the last several years.

Before:

After:

These colors may not work for everyone, but it does demonstrate how you can customize SSMS to fit your tastes.

To get started, from the menu select Tools and then Options.

In the Options window we’ll need to select Environment and the Fonts and Colors.

For Show settings for, select Text Editor and change the font size to 12.

Under Display items, select Plain Text and then click the Custom button next Item foreground.

In the Color window, change the Red to 228, Green to 228, Blue to 228, and then click OK.

Under Item background, select Black from the dropdown list.

Follow the same steps to change each of these other items below.

Text Editor:

  1. Font size: 12

Display Items:

  1. Plain Text
    1. Foreground: 228,228,228
    2. Background: Black
  2. Selected Text
    1. Background: Yellow
  3. Line Number
    1. Foreground: 255, 255, 128
  4. Comment
    1. Foreground: 255, 175, 45
  5. Keyword
    1. Foreground: 127, 176, 228
  6. SQL Operator
    1. Foreground: 199, 199, 199
  7. SQL Stored Procedure
    1. Foreground: 128, 255, 128
  8. SQL String
    1. Foreground: Yellow
  9. SQL System Function
    1. Foreground: 220, 120, 255
  10. SQL System Table
    1. Foreground: 128, 255, 128
  11. String
    1. Foreground: Yellow
  12. URL Hyperlink
    1. Foreground: Cyan

Next, select Grid Results from the Show Settings For dropdown list. Change the font to Microsoft San Serif and the size to 12.

Then select Text Results from the Show Settings For dropdown list and change the font to Consolas and the font size to 12.

Changing the Grid or Text Results will require you to close and reopen SSMS for the changes to take effect. The last thing is to display the line numbers. On the left side of the Options window, select Text Editor, Transact-SQL, and then General. Click the check box next to Line numbers and click OK.

Once you have the colors you want, then go to Tools and click Import and Export Settings.

This wizard allows you to save all the customization settings to a settings file. The file can be used as a backup, or you can use that file to import to SSMS running on another workstation.

Happy customizing!