Effective use of Keyboard Shortcuts with SQL Server Management Studio
This article is aimed at putting together various pieces of info that allow for the better use of the keyboard to make regular tasks in SSMS a bit more easier and effective.
Introduction
There are various tasks involved in our day to day working which are tedious to do with the help of mouse clicks and GUI. For tackling these, we have what we call as keyboard shortcuts. Just think how difficult it would be if we didn't have CTRL+C, CTRL+V for copy/paste but had to do the same using mouse clicks each and every time! It would not only be strenuous but also inefficient. These keyboard shortcuts which aid us in performing tasks in a simpler way not only make our lives easier but productive as well. We would like to cover in this article, the various keyboard shortcuts that can be used with SSMS to make ourselves a bit more productive!
Executing Objects with Keyboard Shortcuts
Whenever we want to view the properties of a table (or any other object) we usually highlight the table_name and press the ALT+F1 keys to obtain the object properties. "Wow! What a magical feature. Don't know how but that just happens!!". This would be the thought for almost all of us during our initial days with SSMS.
These are nothing but the results of the system stored procedure sp_help. Try executing sp_help and the ALT+F1 method for a particular object_name. Both would be returning just the same set of results. So, ALT+F1 is just a shortcut to call this procedure sp_help. Wow! That is so cool - Is it possible to customize this feature for other system procedures / our own set of stored procedures? Of course Yes. Find below the steps for customization of this feature of SQL Server Management Studio allowing us to execute the (frequently used) stored procedures by the press of a key combination.
1. Go to Tools -> Options to open the Options window.
2. Now choose Keyboard under the Environment tab on the left pane. This should now open up the Keyboard shortcuts available.
Note that. by default, the following keyboard shortcuts are configured:
ALT+F1 – sp_help
CTRl+1 – sp_lock
CTRL+2 – sp_who
- Now, choose a particular shortcut available and provide the name of the stored procedure to be executed by the press of the particular key combination. For example, sp_helptext for CTRL+F1.
For example, we have chosen sp_helptext as CTRL+F1. So whenever we're working with procedures, to view its definition, We needn't go for 'EXEC sp_helptext proc_name'. We can just highlight the proc_name and press CTRL+F1 and Voila! Then and there we get the procedure definition in the results pane!!
Other shortcut procedures that we use regularly are:
- sp_spaceused (to view the number of rows of a table)
- sp_helpindex (to view the index properties)
- sp_SQLskills_SQL2005_helpindex – extended version (thanks to Kimberly Tripp :) - Pretty neat proc to list out all the indexes with complete info - included columns, keys etc.) - Get it here.
So, upon pressing CTRL+5 for a particular table, it provides the index details of that particular table as shown below:
Important: Please note that these configuration changes would take effect only in new query windows and not in windows that are already open. So after making the changes, press CTRL+N for a new query window and then go ahead with the shortcuts.
We can appreciate the real use of these shortcuts in practical situations such as in performance tuning. When we are looking at tuning a piece of code, we would want to know the row count/index info of a particular table without changing anything in the current window. Instead of writing a separate query, viewing this info using these shortcuts would prove to be extremely quick, neat and handy!!
Quick Start of SSMS
Instead of starting SSMS through Start -> Programs ->.. or through a desktop shortcut, one can start the SSMS in a much faster way by:
1. Going to the Run Window (Start -> RUN or WINDOWS key + R)
2. Type SqlWb.exe for 2005 and SSMS or SSMS.exe for later versions
3. Press ENTER
Automatically the SSMS is opened up for you. You can take this to the next level by using more options such as -E for connecting be default to the local server under windows authentication and -d to specify the database you want to connect to!!
e.g., typing out SqlWb.exe -E -d tempdb and pressing ENTER would take us directly to the query window!! :) Wow, cool, isn't it!
You have a wide set of options here which you can simply know by typing out SqlWb.exe -? and pressing ENTER. This gives you a screen as below:
Other Keyboard Shortcuts
Apart from the above keyboard shortcut combinations to execute procedures, there are other keyboard shortcuts that would help in writing queries in an efficient manner. The usual Microsoft shortcuts such as CTRL+END for the end of text or SHIFT+HOME to select up to the beginning of line etc. do work with SSMS. We have tried to collate other shortcuts that are native to SSMS, which we have been using on a daily basis.
CTRL+K,C - Press both on a particular line or set of lines to comment (them) out.
CTRL+K,U - Pressing this key combination would un-comment the already commented lines.
CTRL+TAB - to move to the next query window
CTRL+SHIFT+TAB - to move to the previous query window
CTRL+M - Display the Actual Execution Plan (Press again to disable)
CTRL+T - Display results as text
CTRL+D - Display in a grid format
CTRL+R - to minimize/maximize the query results window.
CTRL+G - to get to a particular line number
CTRL+F3 - to search and move to the next occurrence of the highlighted item
CTRL+SHIFT+F3 - to move over to the previous matching instance of the highlighted item
F5 / CTRL+E - to Execute again the selected piece of text
CTRL+F5 - to PARSE the selected piece of text. Not Execute, just Parse.
(Writing a piece of code for a friend / Forums but can't test it due to object unavailability but want to ensure its syntactically right? This Friend PARSE can help you!! )
CTRL+U - to go to the database drop-down menu
TAB - select a set of lines and press TAB to indent all these lines to the right with a TAB space.
SHIFT+TAB - same as above, but the reverse. Indents on the left.
(very useful while indenting a code for readability)
CTRL+SHIFT+U - Transform the selected text to UPPER case
CTRL+SHIFT+L - Transform the selected text to lower case
Another interesting shortcut is CTRL+C. In SSMS, we needn't select the text but just press CTRL+C to copy the entire set of text on the line having the cursor. Then the usual CTRL+V to paste the copied text.
Important: Please note that all these shortcuts are almost the same until SS 2008 R2. In SS 2012, some of these have changed a tad bit. What we have listed here is just a set which may be useful on a daily basis. You can always obtain the complete list of shortcuts here at MSDN.
Conclusion
We have seen as to how the keyboard shortcuts can be used effectively to do day to day operations in SSMS. These cover - regular keyboard shortcuts, shortcuts native to SSMS, keyboard shortcuts for quick execution of procedures and methods to quick start SSMS.
Hope these shortcuts and small yet useful tricks make our coding experience more fun and effective!
See Also