T-SQL Tuesday #68: Changing SSMS Keyboard Query Shortcut Defaults

Welcome everyone!

TSQL2sDay150x150
After being a long time read and occasional participant, I am honored and humbled to be your host for this month’s T-SQL Tuesday! In my Invitation to T-SQL Tuesday, I asked everyone to write a blog having something to do with “Defaults.”

For my contribution, I decided to blog a bit about SQL Server Management Studio. As a SQL Server Developer, I live my life within SSMS. As much as I like it, there are a variety of defaults that I like to change. And for today’s blog, I’m going to talk about Keyboard Query Shortcuts.

If you navigate to Tools -> Options -> Environment -> Keyboard -> Query Shortcuts, you will find that SSMS comes with a handful of defaults.

1_Options
Tools -> Options

2_Query Shortcuts - Default
Query Shortcut Defaults

These three starting shortcuts are indeed very handy, but SSMS gives us a number of other blank entries to add our own. Wouldn’t it be a waste if we did not take advantage of them, to add in some of our own?

2_Query Shortcuts
My Default Changes

As you can see above, here are ALL of the new Keyboard Query Shortcuts that I add. And here they are in full:

  • Ctrl+3 = sp_help
  • Ctrl+4 = sp_helptext
  • Ctrl+5 = sp_SQLskills_SQL2008_helpindex
  • Ctrl+6 = sp_SQLskills_SQL2012_helpindex
  • Ctrl+7 = EXEC sp_whoisactive @get_plans = 2, @get_transaction_info = 1, @get_task_info = 2, @get_avg_time = 1, @get_outer_command= 1
  • Ctrl+8 = EXEC tempdb.dbo.sp_help
  • Ctrl+9 = EXEC sp_helpExpandView @ShowObjectCount = 1, @ViewName =
  • Ctrl+10 = EXEC sp_whoisactive @get_plans = 2, @get_transaction_info = 1, @get_task_info = 2, @get_avg_time = 1, @get_outer_command= 1, @delta_interval = 10

Let’s explore how I make use of each of these!

sp_help & sp_helptext

I use these two SQL Server built-in stored procedures on an almost daily basis. They’re extremely useful while in the midst of code, to check aspects of objects. How do you make use of it? Simple! All you have to do is highlight the object in question, then hit your Keyboard Shortcut!

3_sp_help

Using sp_help, I can quickly peruse all available columns in a given object, double-check their datatypes, or take a quick glance at what foreign keys and constraints are in place.

4_sp_helptext

With sp_helptext, I can quickly scan the contents of a view or stored procedure, when I don’t want the hassle of finding it & script it out via the Object Explorer.

sp_SQLSkills_SQL20xx_helpindex

SQL Server comes with a built-in stored procedure: sp_helpindex. sp_help also returns index information about a table. However, they don’t return everything. When I’m query tuning and checking underlying indexes, I need to know about INCLUDE columns. That’s where Kimberly Tripp’s sp_helpindex rewrite comes in.

5_sp_sqlskills_helpindex

Used in the same fashion as sp_help & sp_helptext, I now have in-depth index information available at my fingertips!

sp_whoisactive

“Hey, why’s the server running slow right now?” Every time I hear that question, Adam Machanic’s sp_whoisactive is the very first thing I always run. If you don’t use this tool already, read more about it on Adam’s blog and install it NOW!

While you can run it without parameters, I like having some additional data points available to me, which require parameters. As you can see from the above command list, that’s a heck of a lot to memorize and re-type, so I’ve mapped that to Control-7.

tempdb.dbo.sp_help

If you’re like me and work with a temporary tables regularly, you’ll notice that the usual sp_help shortcut does not work. Not having sp_help information conveniently available for temporary tables is rather obnoxious, hence this particular Query Shortcut.

6_sp_help_temptbl-1
Executing sp_help against a temporary table, in a user database context, returns an error.

6_sp_help_temptbl-2
But with a modfication to execute from tempdb, we can get our sp_help data again!

Now I can have the same sp_help functionality and convenience with my temporary tables!

sp_helpExpandView

My final Query Shortcut executes my custom tool sp_helpExpandView. Used in the same way as sp_help, I can quickly reference underlying objects of a view. If you have to deal with untangling nested views, I would encourage you to check it out!

That’s it for my SQL Server Management Studio Keyboard Shortcuts. I hope you all found this insightful. If there are any Defaults that you like to change in SSMS, please feel free to share in the comments. Thanks for reading – until next time!

Advertisements

Learn Something New – SSMS Tips & Tricks

TSQL2sDay150x150

Today’s post is not only for the SQLNewBlogger Challenge, but is serving double duty as my T-SQL Tuesday contribution! This month’s T-SQL Tuesday is being sponsored by Mike Donnelly (b|t) and he has asked us to “Teach Me Something New.”

This past weekend, I participated in my third SQL Saturday Madison event. Aside from speaking, I attended some sessions and guest-hosted the SSMS Showdown between Jes Borland (b|t) & Russ Thomas (b|t). Jes & Russ had a fantastic showdown of a bunch of neat SSMS related tips and tricks. Here’s some of the new things I learned about!


OBJECT EXPLORER FILTERS

This is a feature that I thought was rather neat.  I never really noticed the little filter icon – it just sort of blended in I guess.  But it is fantastic if you have a database with hundreds or thousands of objects, perhaps that is not sorted or named very well.

filters-1

If you select a category folder, like Tables, the Filter icon becomes enabled.  Clicking that will bring up a window which gives you a number of different options to filter on.

filters-2

Next add some keywords. I added Phone and Person and voila, my Tables now show as being filtered and only the tables that matched now appear in Object Explorer!


CLIENT STATISTICS

The next feature I (re)learned about is Client Statistics.  I always seem to forget that this exists, but it is a very insightful feature for performance tuning.

client-stats-1

Turning it on is simple. You can access it from Query Menu, a keyboard shortcut, the toolbar.

client-stats-2

Then I ran a query. I varied and re-ran my test query 4 more times. Now you can see from the next screen shot that different data points about my last 5 executions were saved. Client Statistics will save the 10 most recent queries you’ve ran. It will also average them in the final column.

This is extremely useful to see how much work your queries are really doing, and as you’re tweaking and tuning, how each iteration may differ!  Great stuff!!


SPLIT WINDOW

Ever find yourself looking at a long query or stored procedure, and need to reference something at the top along with something toward the bottom?  Isn’t it a pain to scroll back and forth?  Well in SSMS, it seems you can split an existing query window!

I’ve opened a stored procedure from AdventureWorks, that has a CTE of a UNION of two queries.  Let’s say I am trying to troubleshoot something and want to compare the output column declaration list to the final output of my final query.

split-1

Second half:

split-2

Instead of scrolling back and forth, try using the handy split screen functionality!

split-3

Click & hold the split screen icon on the upper right, and drag down.

split-4

Voila!  Your window is now split in two, and you can now scroll to independently and have two different views of your query!


Well I hope you enjoyed these SSMS Tips & Tricks.  Special thanks to Jes & Russ again, for inviting me to guest-host their session and teaching me these nifty tidbits!