Helpful tips and tricks for SSMS

I have been busy working on another project and haven’t been able to get a post together in a bit. So, I wanted to put something quick together for everyone. These are some of the tips and tricks that I have learned over the years and have found uses for almost everyday.

Adding line numbers

Line numbers are key to debugging code. It is the reason code editors tend to have them on by default. But, this is not the case with management studio. To add these select Tools -> Options menu item. In the dialog box that appears click on Text Editor and then Transact-SQL. In the display section on the left, select the check box for line numbers

Inserting a comma separated list

Every select statement needs a comma separated list of columns you would like to return. But, this can be a pain to type especially for many columns. But, what I tend to do is just grab all the columns in the tables and select the blocks I do not need. Now some of you are stating, but why in the world would you write those all out???

The trick is not to write them out to begin with. Drag the ‘Columns’ item in Object Explorer and drop it onto to a query window. This generates a list in your query window. Just add the select, from, and where clause.

Selecting a block of text

Now that you have all the columns from your tables, you need to remove some. Instead of just highlighting the text and deleting the rows. Try to hold the ‘alt’ key and highlight the text. This allows you to select the block of text and remove it. That’s pointless, why would anyone need this?

The best use case for this is to remove the schema/table references at the front of your columns.

Inserting a block of text

Now that you can select this text, there is always one question that comes up. Inserting text, how?

To do this, add the ‘shift’ key along to your ‘alt’ key. Once held down, use the directional keys to navigate up and down. This makes aliasing tables easy. A great Gif can be found on Brent Ozar’s Website.

Color coding connections

Finally, many of us tend to work on every server at once. This can lead to some confusion when trying to execute queries on a specific box. This can be great to label different boxes specific colors. SSMS has the ability to color code specific connection. This displays at the bottom of the query window.

E.G. Green for test or Red for Production for example.

When connecting, select the Options button in the Connect to Database Engine window. Then select the Connection Properties window. Select the check box towards the bottom of the window and use the ‘Select…’ button to choose a colour.

Leave a Reply

Your email address will not be published. Required fields are marked *