Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

My favorite lesser known features of SSMS

A few days ago Mickey Stuewe and I were talking on twitter if there was a way to generate an alter view script from object explorer using only keystrokes.

Turns out you can mimic a right click with Shift+F10 – the rest is just hitting keys of first letters in the dialog menu S = (Script) A=(Alter) N=(New).

She got me thinking however about my other favorite hidden gems inside SSMS.  What are some of the things I use from time to time that might not be widely known or used?


Templates

template

Take a look at the template viewer under the view menu.  You can also just hit ALT-T.

The collection of templates is nice – it’s a quick way to access stubbed out TSQL code blocks.  But my favorite use for the template viewer is the ability to make my own.

I create a node called AAA DBA Toolbox (so that alphabetically it appears near the top).  I can then drop some of my more frequently used queries in there.  This is what it might look like.

Kudos to @BrentO who gave me this idea a few years ago.


Connection Colors

In my current position we use RedGate tools and make use of their connection coloring.  It’s a great way to have a visual indicator that you are in production, test, state, dev etc.  No more – “oh crap – I just ran that in production” moments.  Well, maybe just fewer moments like that.

Almost as nice as the RedGate version is the one already in SSMS.  You can find it by clicking options on the connection box – then connection properties – and checking the connection color.

con_colors

After you enable the connection color the status bar will appear in that color on future query windows:

concolorsample


Column Based Copy / Paste

Once upon a time I made a living off CA Clipper – a language closely related to dBase.  Our code editors were not much better than notepad.  We finally upgraded to a 3rd party editor, Multi Edit I think and I was blown away by the impact it had on the coding experience. It had a column based copy and paste feature that I used all the time.  If you ever worked in clipper or xBase you can probably relate.

A few years ago I discovered that SSMS has a columnar copy paste as well.  Alt+Shift + arrows or mouse to select and copy (ctrl+c) and then place the cursor and (ctrl+v) to paste.  Every now and then this little gift from above saves a million keystrokes.

ALT+SHIFT + < arrows or mouse to select >

columnpaste


Detailed Object Explorer

Another one that for whatever reason I didn’t stumble upon until recently is the detailed object explorer.  If you’ve never looked at this view hit F7 on SSMS.  It’s similar to the object explorer that opens by default but with a large amount of additional information.

The table node is shown here – shows create date, schema, filegroup, rows, space used etc.

detailexplorer

Drill down into the database node.  You can see in one single view recovery modes, compatibility levels, space metrics.  I usually like to do things via script as opposed to the GUI but for quick glances, I gotta say F7 is pretty trick.


Zooming Deadlock Graphs

cantreadthis

Have you ever tried to look at a deadlock graph in SSMS?  Open up an XDL file.  Small ones are ok – but occasionally you might run up against something that has intra-query parallelism or a chain that is several nodes long.  Suddenly the graph is huge.  It took me forever to find how to zoom the dang thing.

There are some very nice third party tools to look at deadlocks from companies such as SQLSentry but if you need to zoom within SSMS the secret is – Zoom = ctrl + mouse wheel up (wheel down to zoom out)

 


And last but not least, the complete collection of hot-keys documented in books online:

I’m a sucker for anything with a hotkey.

http://technet.microsoft.com/en-us/library/ms174205.aspx 

5 comments on “My favorite lesser known features of SSMS

  1. Pingback: (SFTW) SQL Server Links 21/02/14 • John Sansom

  2. Justin Larson
    February 24, 2014

    you are my personal hero for a day for that column select/copy/paste tip.

    • Russ Thomas ( @SQLJudo )
      February 24, 2014

      Nice – that is one of those one’s that when you need it…. you really need it. Glad it helped.

  3. Brent Ozar
    February 28, 2014

    Awww, glad I could inspire you on the templates!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on February 17, 2014 by in Career Skills.
%d bloggers like this: