Welcome everyone!
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.
Tools -> Options
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?
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!
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!
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.
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.
Used in the same fashion as sp_help & sp_helptext, I now have in-depth index information available at my fingertips!
“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.
Executing sp_help against a temporary table, in a user database context, returns an error.
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!
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!
Every Byte Counts 