T-SQL Tuesday #68: Round-Up

A couple of weeks ago, I posted an Invitation to T-SQL Tuesday #68: Just Say No to Defaults. Last week, #SQLFamily responded in force with 22 blog posts! There’s a fantastic wealth of information across all of these blog posts and I was blown away!

Without further ado, here’s the round-up!

I want to thank everyone for participating and sharing your knowledge. And I hope that those of you who are reading, but not yet blogging, will join us, start your own blog, and begin sharing your knowledge and your stories too!

Until next time!

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!

Invitation to T-SQL Tuesday #68: Just Say No to Defaults

TSQL2sDay150x150
Welcome to another edition of T-SQL Tuesday! Started by Adam Machanic (b|t), this is the SQL Server community’s monthly blog party, where everyone is invited to write about a common topic.

I am honored to be your host this month. This month, I’d like to ask everyone to blog about SQL Server Defaults. While SQL Server CAN be run with just the default settings, doing so is far from ideal. As we progress through our careers, many of us build lists of things we change. Let’s blog about those! And don’t think this month’s topic is constrained to just the SQL Server engine. SSIS & SSAS are absolutely fair game as well. SQL Server developer tools – sure! Want to take a different spin on “defaults,” go right ahead and be creative!

Need Ideas?

Do you have a checklist of “always change” settings? Blog about that!
Do you have a preferred method for mass-deploying your changes? Blog about that!
Do you have a specific default setting you ALWAYS change? Deep dive & blog about that!

Want to Participate?

  1. Write a blog post about the topic. Don’t have a blog? Start one!
  2. Include the T-SQL Tuesday Logo and link it back to this post.
    TSQL2sDay150x150
  3. Publish your blog post Tuesday, July 14, between 00:00 GMT & 23:59 GMT.
  4. Leave a reply below with a link to your blog post (for the round-up).
  5. Share you post with the community! Tweet it out using the #tsql2sday hashtag!

Let’s have some fun! I’m looking forward to everyone’s contributions & learning something new!