Thursday, August 16, 2007

Query Shortcuts in SQL Server 2005

There is a nice feature which I did not come accross until recently. That is Query Shortcuts. This can be very helpful when executing regularly used queries.

For example consider SELECT * statement that we use.

When ever we need to get the full record set of a table we are writing SELECT * FROM tblTable. So if we have 10 tables we need to write the same SELECT * FROM part 10 times or we may need to copy and paste that. Also mosstly, as developers we are using this statement for temporary purposes to just see what is there inside the table later we will delete the queries. In such cases the query shortcuts come in handy.


But to use them you have to set them up.
1. Open SQL Server Management Studio (SQL SMS) and go to Tools -> Options..., this will bring up the Options page.
2. In that, expand the Environment category and click on the Keyboard.
3. Now select a key combination which is easy for you to use (I selected Ctrl + 3.). Then in front of the key combination under Stored Procedure type SELECT * FROM and press ok.

Setup is done, now we'll use it.
4. Now open a new Query Window in SQL SMS. Then drag a table from the Object Explorer.
5. While the table name is selected, press the key combination (My case it is Ctrl + 3.).


See what happened?

It will run the SELECT * FROM tblTable query even though the query window is just having the table name.
Not only SELECT * FROM you can run what ever queries by using this method. But a limitation which I see is that we cannot create our own key combinations (I prefer assigning Ctrl + S or Ctrl + Space for SELECT * FROM rather than Ctrl + 3.).
I think this is useful to many developers to improve their productivity.

9 comments:

Anonymous said...

I have tried this. But it is not working. I feel, instead of typing 'select * from' in the keyboard shortcut, create a SP and put the select statement inside. Then give the SP name in the shorcut. If i'm wrong correct me.

Arjuna said...

I tried this and it works in SQL 2005.
What is the error you are getting?
Sometimes it might be that the table you are trying is under differnt owner. So try typing in the owner name and the table name and selecting both of them.
Or else you also can login using that owner and just use the table name when quering.

Anonymous said...

As u told, i have written "select * from" in the keyboard shorcut. Opened a new query editor and typed owner.tablename. Then selected the name and pressed Ctrl+3. I'm getting error "Incorrect syntax near 'from'" .

Arjuna said...

I think you are using Windows XP. I have heard that under windows XP there is a problem with SQL Query shortcuts.
Unfortunately at the meoment I couldn't find a Windows XP machine with SQL 2005. When I comes across a one, I will definitly have a look about this and will give it a try to finding a solution.

Anonymous said...

Yes. i'm using windows XP. Possibly it could be the reason.

Anonymous said...

u need to write the complete query.
ex: select * from tablename

Anonymous said...

Yes in Windows XP Its not working . If anybody has any update let us know .

Anonymous said...

has anyone figured out how to run this with multiple parameters? eg, put a procedure name that takes two parameters in the shortcut...highlighting multiple params and trying to run it does not work.

Anonymous said...

Handy tip, thanks.