Ok, this is a quick SQL Server serendipity that has saved me lots of little increments of time that add up over the long haul… call it productivity if you will. I stumbled on this little ditty a few years ago. FYI, if you don’t already know, sp_helptext is a standard system stored procedure that returns the text of functions, views, and procs*. Typically you call it from a query window like so:

^?View Code SQL

1	sp_helptext 'vwSomeRandomView';

It’s a very helpful tool. How often are you pouring over code and have to dig into some function or proc to see what it’s doing? Sure, you could go to the SSMS object explorer… expand your database… then Programmabilty… (is that a word?), then Stored Procedures (yawn), then right click on it after you’ve found it in a list of likely hundreds, then finally navigate a popup menu of multiple options and have it written out to a new window. What is that? Like 5 minutes of work? Well, seriously… it’s a lot of clicking for such a common task for any DBA or developer. So if you’re a little more senior you’ve already taken on the time to learn what tools are available to you in T-SQL land. sp_helptext is a fundamental part of that Batman-esque utility belt.

Although it’s great to know it and use it, I still have to open a window and type all that out. It’s not as bad as the previously mentioned work flow but wouldn’t it be better to have a hot key where I can just double click a proc name or function or view once found in my trusty query window and just have that text come to papa? That’s how I thought it should work if I could have it Burger King style. Well, it’s your luck day. This my friend is a reality.

Ok, let’s do the set up.

First you’ll need to get to your options window for SSMS. Tools,options:

ssms options

Then navigate to the “keyboard” option in the hierarchy:

options modal window

You’ll see a list of hot key slots and their currently assigned stored procedures. Typically you get “sp_help” assigned to Alt+F1 and “sp_who” to Ctrl+1 right out of the box. These are also very valuable items to have in your hot key arsenal. Besides, knowing hot keys is a subtle way to look mega cool to your geek cohorts… without all out bragging.

The key to this trick is the under-published fact that when you highlight text in SSMS and click a hot key combo, that text is passed as the first parameter to the call defined for the hot key**… I know! Totally killer. And yes… this means you can design your own procs that can accept highlighted text as parameters. But I’m getting ahead of myself. The final step here is to insert “sp_helptext” into a hot key slot (wow… that actually sounds really bad… maybe that’s why MS calls them shortcuts). I like “sp_helptext” to be Ctrl+3… but that’s me. Click “Ok” and give it a whirl. Double click or use your cursor to highlight the name of a coded object in your query window, and then press your chosen hot key combo and tada… the text is in your results pane.

sp_helptext results

If you just want to take a glance at the code you could use the system shortcut Ctrl+T just beforehand to instruct SSMS to display the results in text format as opposed to a grid. However, if you plan on copying the results into another window it’s best to leave it in the grid. The text output by default prefaces the stream with “Text” as a column header and a row of hyphens.

After a while you’ll get proficient double clicking your objects, slapping that hot key, then a quick Ctrl-c (copy) followed by a knock out combo Ctrl-n (new window) and Ctrl-p (paste) and have that puppy at your disposal in a new query window in a few measly milliseconds. You can thank me later.

Happy SQLing.

*sp_helptext can also return the definition of a computed column in a table but since that requires input for a second parameter, it doesn’t work for this little trick.

**Actually, I just realized that if you highlight a delimited list of parameters they are all properly passed to the shortcut procedure correctly, although that’s pretty much overkill and not so useful for intended purpose.