Ctrl+1 in SSMS for sp_who, plus more

August 7, 2008

I did this by accident, but turns out it’s a really useful feature. I was just trying to zoom in on something using Zoomit, that really useful thing that I use whenever I’m presenting these days. But I didn’t have Zoomit running, and so SQL Server Management Studio ran sp_who.

And it’s not even new. This is an old Query Analyzer thing – I just hadn’t come across it before. And it’s extensible! Go to Tools, Options, Environment, Keyboard. By default, sp_who is Ctrl+1, sp_lock is Ctrl+2 and sp_help is Alt+F1 (which runs on whatever you have highlighted). Then pick one of the ones that you’re allowed to set for yourself, and put a command in. I can’t believe I haven’t stumbled across it before. I’ve already put sp_helpindex on Ctrl+3, and sp_helptext on Ctrl+4.

The only complaint I have about it is that if you select a two-part object name and hit Alt+F1 (the shortcut for sp_help), it fails because there aren’t quotes around the name. I’d like to be able to wrap stuff up, to make it do “sp_help ‘*'”, where the star refers to the selected text. I thought this would be worthy of posting to Connect, but Michael Swart has already posted something similar. No-one’s voted on this yet, but I think it could be really useful (so please, go vote. Put comments in, all kinds of stuff). Great to be able to call sp_helpindex when highlighting a table (I’ve just added this one), but if this breaks whenever I need to specify a schema, then it’s just a little less useful. I’d even like to be able to have something which runs a whole query, using my highlighted text somewhere in there.

At the moment I’m playing around with having a keyboard shortcut for:

select * from sys.dm_db_index_usage_stats

and then highlighting:

where object_id = object_id(‘sales.salesorderheader’)

What do you have on your keyboard shortcuts?

This Post Has 2 Comments

  1. Fabiano Neves Amorim

    Hi Rob,

    Yeah, in my environment I use a lot of shortcuts

    Here is,

    Ctrl-3 = sp_helpindex
    Ctrl-4 = sp_spaceused
    Ctrl-5 = select top 10 * from — That is very cool

    And the wished,

    Ctrl-7 = select * from DictionaryTable where name = ‘?’

    In this case I use,

    select * from DictionaryTable where name =

    Than when I need use this, I put myself the ‘’ in selected text.

    ‘tabtest’ then run crtl-7

    I was thinking in develop one Add-In to SSMS to fix this… what do you think?

  2. robfarley

    Ah, love it. Particularly the SELECT TOP 10 * one.

    An add-in would be great. Let me know if you get something sorted.


Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.


Related Blogs