Introduction

I know I said I was going to add more information about the CDC system in this post, but I wanted to throw up a quick sideline before Christmas.

SSMS is a powerful tool, and one I am very happy with. It contains a lot of really useful features that many people don't know about. Everyone with a SQL Server related blog seems to have at least one post containing their favourite SSMS features, so here's mine

A few keyboard shorcuts

Let's start with the obvious. Keyboard shortcuts.

CTRL + D will put you into grid results mode.

CTRL + T will put you into text results mode.

CTRL + R will show or hide the results pane.

CTRL + K, C will comment out a selection. CTRL + K, U will uncomment the selection.

I also like to assign a keyboard shortcut for "change connection".

One more thing that's sort of a keyboard shortcut, although it's not specific to SSMS. Let's say one day you have object explorer open at the table valued functions node of one of your dozen or so databases on one of your dozen or so instances, and someone comes along looking for a stored procedure on another instance. Great, it's going to take you forever to collapse all those nodes to find the instance you want, or you can try to scroll through your hundreds to objects. OR, you can just click on any node in the current object explorer view, and hold down the left arrow key. This will collapse the node all the way back to the root almost instantly.

Using ALT+select in the Query Editor for Formatting

This is hands down my favourite thing about the SSMS text editor (maybe #2 behind syntax highlighting, but everyone already knows about that, and surprisingly few know about this). It's a feature many text editors have, but a lot of people don't know it's available in SSMS.

The best way to show you what it does, and how useful it is, is by way of example. I'm an SQL formatting nazi. I like to have objects of similar types horiontally aligned with vertical rivers (see more about that in my post on SQL standards).

So let's say you have a query that looks like this:

(although it probably won't have my awesome colour scheme. If you want that, it's here: My fonts an colours registry settings for SSMS 08. Just rename the extension to .reg and double click to import. You may want to backup your current settings first, find those at HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\FontAndColors):

Then you realize you need to add a left join. OH NO, ZE FORMATTING, SHE IS RUINED!!

Alt select is here to save you. Hold down the ALT key. This puts text selection (by mouse or keyboard) into row and column mode instead of line mode. Now you can drag a box around the part that needs to move. You don't actually have to select all of the text, as you can see here:

Now hit the tab key. VOILA! Crisis averted.

Using ALT+select in the Query Editor for Editing

You can also use this feature with copy and paste. To demonstrate, let's pretend I'm doing a restore.

[pref]

restore database DW

from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\Backup\DW.bak'

with recovery, replace, stats=1

[endpref]

But... oh no, your database is composed of a lot of files, all of which specify the wrong file path in the backup. Well, you have a long series of copy and pastes ahead of your, or a lot of typing, right? Wrong! Just write up something with more than enough template move lines. Now press CTRL+T to go into text results mode (which by default is column aligned) and do a restore filelistonly from the backup. Hold down the alt key and select the logical file name column. Your window will look something like this:

Now just put the cursor in the first empty string for the logical file name, hit paste, and watch the magic. Then ALT+select just the file names (without the paths) from the physical file name column in the result set, put the cursor at the end of the first incomplete path you wrote in your move template, and paste that in as well. Job jobbed!

Addons

There are also a few great addons for SSMS. My favourite is still the SSMS tools pack. The window connection colouring feature takes all of the paranoia away when working with multiple systems at once.

I also have at least one really great idea for an SSMS addon of my own. DBA's, you're going to love it. Stay tuned!