The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
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?
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.
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.
After you enable the connection color the status bar will appear in that color on future query windows:
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 >
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.
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
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.