THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know: You can define your own Hotkeys!

One feature of SQL Server 2005 Management Studio that I have been known to complain about is the mechanism behind customizing your own hotkeys. Oh, yes, there is an interface for it, but once you've defined a key sequence, it doesn't work like I wanted it to.

In the Management Studio, go to the Tools menu and choose Options. Under Environment, choose Keyboard and you should see a list of possible hotkeys. Three are predefined

ALT+F1 will execute sp_help
Cntl+1 will execute sp_who
Cntl+2 will execute sp_lock

But what if you want to use the new SQL Server 2005 metadata? What I would like is the ability to enter a partial command, and when I enter the hotkey sequence, that text would be copied to my query window. I could then fill in the blanks, so to speak. As it is, the only hotkey I had defined was one that mapped to DBCC FREEPROCCACHE.

I would like to map a key sequence to "SELECT * FROM" and when I enter the keys, the text would appear and I could enter the view or table name. But I can't do that, because SQL Server immediately tries to execute whatever text is associated with the key sequence, and "SELECT * FROM" is illegal syntax, in any version.

(I actually have a vague memory of some prior version of SQL Server allowing me to just enter part of command, and I could polish it up before running. I thought it was Query Analyzer, but I just tested it, and it wasn't true. )

In fact, I just discovered that Management Studio does the QA one better. When I was pointing out the limitations of this behavior in class last week, one of my very bright students discovered that if I have a view or table name highlighted in the query window, I can then use a hotkey sequence defined as "SELECT * FROM" and SQL Server will take the highlighted text and append it to "SELECT * FROM". So if I have sys.dm_tran_locks highlighted in the Query Window, entering my hotkey sequence will execute "SELECT * FROM sys.dm_tran_locks". Now of course I would still prefer to have the text copied to the screen so I could make other changes if desired, but this is sure better than nothing.

(In the SQL Server 2000 QA tool, even that doesn't work. If I highlight a view or table name, and hit my key sequence for "SELECT * FROM", I still get the syntax error "Incorrect syntax near FROM". )

Have fun!

~Kalen

Published Sunday, January 27, 2008 4:57 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Simon Millar said:

Check out Red-Gate's SQLPrompt tool - I think it does exactly what you are looking for, e.g. use the key sequence "ssf" can be expanded to "SELECT * FROM"

January 27, 2008 7:24 PM
 

Kalen Delaney said:

Wow, this is the fastest I ever got a comment on a blog post.

Thanks for the tip, Simon. I think I tried out a early version of the Red Gate Tools years and years ago, but I haven't looked at them in a while.

January 27, 2008 8:04 PM
 

Brennan said:

Just finished a trial of SQLPrompt and its a big improvement on the version I trialed years ago.

January 28, 2008 4:35 AM
 

Theo Spears said:

Hello Kalen

Echoing what Simon and Brennan said, if you have not tried SQL Prompt recently I would recommend taking another look. The team here worked hard on it through most of 2007 and the latest version (3.6) is much better than 3.0. If you do find it useful you might want to take a look at the Friends of Red Gate program - as an MVP and SQL Server blogger you may be eligible to get free copies of all our tools.

Theo Spears

Red Gate Software Ltd

January 28, 2008 5:28 AM
 

Merrill Aldrich said:

For pure entertainment value, type YELL<tab> into SQL Prompt...

January 28, 2008 2:15 PM
 

Gregg Stark said:

January 28, 2008 4:46 PM
 

jerryhung said:

SQL Prompt has been quite helpful since we bought it as part of Toolbelt

It's not something I can't live without, especially with IntelliSense coming in SQL 2008 SSMS

SQL Factor, on the other hand, has failed more than it worked

I always get this "Working ... 0%" frozen window, causing me to kill it in Task Manager and exit SSMS

January 29, 2008 11:17 AM
 

Sankar Reddy said:

Someone on the web (I forgot his website) told me this is possible and I have been trying this to make this work and I haven't been successful. I thought that guy was faking it. I am sorry whoever you are, if you are reading this.

Strangely enough this works for me on some computers and it doesn't work on others. May be few ppl can test & confirm it.

Works on:

1) Windows Vista

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)

Feb 9 2007 22:47:07

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Evaluation Edition on Windows NT 6.0 (Build 6000: )

2) Windows Server 2003

Microsoft SQL Server 2005 - 9.00.3054.00 (X64)

Mar 23 2007 18:41:50

Copyright (c) 1988-2005 Microsoft Corporation

Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

Doesn't work on:

1) XP Pro

Microsoft SQL Server 2005 - 9.00.3054.00 (X64)

Mar 23 2007 18:41:50

Copyright (c) 1988-2005 Microsoft Corporation

Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I know that I am doing the exact same thing in all of the computers.

January 29, 2008 7:17 PM
 

Jared Ko said:

This is very cool! Prior to this, I was using utility stored procedures to accomplish the same thing. I actually had a procedure in my development environment that would 'SELECT TOP 1000 * FROM ' + @TABLE_NAME

I can now eliminate these types of procedures from my code.

Thanks Kalen (and thanks Gregg for more details)

January 29, 2008 8:30 PM
 

Dave said:

I just discovered this post from a google search.  Like you, Kalen, I would rather it just insert SELECT * FROM into the query window.  To get close to that, I entered SELECT 'SELECT * FROM  WHERE' then I just Ctrl-C the results and Ctrl-V into the window.  Not ideal, but I'm going to give it a shot.

December 16, 2008 11:14 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement