By: Arshad Ali | Updated: 2011-06-09 | Comments (6) | Related: 1 | 2 | 3 | 4 | > SQL Server Management Studio Shortcuts
Problem
As DBA and Developer responsibilities grow on a daily basis, we how can we improve our productivity? Due to the time it takes to use the toolbar, menu bar or mouse, do I have any other options? Are there keyboard shortcut for SQL Server Management Studio? Could these help me improve my productivity? Check out this tip to learn more.
Solution
We often overlook different SSMS shortcut keys which provide a boost in DBA and Developer productivity. In the second tip of this series (SQL Server Management Studio keyboard shortcuts - Part 1), I am going to further explain shortcut keys for managing Intellisence, debugging, running your code and many more. Let's jump right in.
SQL Server Management Studio Intellisense
SQL Server 2008 and later versions include IntelliSense which let's you know about objects in the database, supports T-SQL syntax, gives the parameter info of the stored procedures or functions. Although IntelliSense works by default (you can enable or disable it as and when required), there are times when you want to manually list a set of objects or members. This can be accomplished by pressing CTRL+J as shown below:
Stored procedures and functions normally accept parameters, when you want to execute a stored procedure it is good to know the name, data type and number of parameters to pass it for execution. To accomplish this, press CTRL+SHIFT+SPACE after the stored procedure name to view the list parameters as shown below:
To minimize the typing effort, you can use the auto complete feature of IntelliSense. Simply press ALT+RIGHT ARROW and it will complete your word by the best and first matching member list. If it finds more than one item it shows a list as shown below to choose one. You can also press CTRL+SPACE to bring up this list.
When we first connect to the database, list members are queried and stored in the local cache for better performance. Unfortunately, if someone creates additional objects, those new objects will not be listed. When this occurs, you need to refresh the local cache. Refreshing the local cache can be accomplished by pressing the CTRL+SHIFT+R keys or you can also perform this actions using the menu bar (Edit | IntelliSense | Refresh Local Cache) shown below:
Execute Scripts in SQL Server Management Studio
Now lets move on and see how to execute scripts using the shortcut keys. If you want to parse all the scripts without executing them in the current query window, simply press CTRL+F5 or you can select some lines of code to be parsed then press CTRL+F5. If you want to execute all the scripts in the current query window simply press F5 or you can select some lines of code to be executed then press F5. After the execution a result pane appears on the bottom section of SSMS. You can press CTRL+R to toggle the result pane as shown below. As you can notice in the image below it has a Results tab which shows the result sets returned after execution of the query and a Messages tab which shows all the messages generated/printed during execution, for example number of records affected etc.
By default the result set is displayed in the grid format, but you can change it to display as text or send the results directly to a file. To change the setting to display results as text, simply press CTRL+T before execution and result would display as shown below next time when you execute your script. To send the results to a file press CTRL+SHIFT+F before script execution. If you want to revert the setting back to return the results in a grid you can press CTRL+D before script execution and next time you will see your results in the grid.
Query Execution Plans in SQL Server Management Studio
During query analysis you might need to know the execution plan of a query even before executing it. To find this out, press CTRL+L to display the estimated query execution plan as shown below without actually running the whole query (in fact it runs for top 1 row).
On the other hand, you might need more detailed information and review the query execution plan with the query execution results. To capture this information, press CTRL+M to include the actual execution plan as shown below with the query results. You can even press SHIFT+ALT+S to include the client statistics with the query results.
If for some reason you would like to cancel a long running query, for that hit ALT+BREAK to cancel the current query execution.
SQL Server Management Studio Debugging
In SQL Server Management Studio, you can debug scripts. To start debugging from the first line press ALT+F5. To toggle a breakpoint on the line simply traverse to that line and press F9. To delete all the breakpoints from the query window at once, press CTRL+SHIFT+F9. A breakpoint is denoted by a tiny red circle placed on the left side of the query window as shown below:
To manage all the breakpoints in one place, SSMS has Breakpoint window as similar to the Bookmark window. To launch the Breakpoint window press CTRL+ALT+B and you will see one window as shown below:
While debugging you can press F11 to step into the called module or F10 to step over the called module. You can also press F5 to go to the next breakpoint instead of going one statement at a time.
Sometimes when you are working on a long script in the SSMS query window, it might appear small for you, in that case you can hit SHIFT+ALT+ENTER to toggle the query window in full screen mode as you can see below:
For help, you can select the object and press ALT+F1 to display the meta information about that object stored in the database as shown below.
If you are looking for help in Books Online you can select the keyword and press F1 to launch BOL with information about that selected keyword if it exists.
Accessing common windows in SQL Server Management Studio
Here are the shortcut keys for common SSMS windows:
- F8 - Object Explorer
- CTRL+ALT+T - Template Explorer
- CTRL+ALT+L - Solution Explorer
- F4 - Properties window
- CTRL+ALT+G - Registered Servers explorer
Standard shortcut keys
Please note apart from the above mentioned shortcut keys here are some standard shortcut keys which work fine in SSMS:
- CTRL+A - Select all the text in the current query window
- CTRL+C - Copy text in the current query window
- CTRL+V - Paste text in the current query window
- CTRL+X - Cut text in the current query window
- DEL - Delete text in the current query window
- CTRL+P - Launch the print dialog box
- CTRL+HOME - Top of the current query window
- CTRL+END - End of the query window
- CTRL+SHIFT+HOME - Select all the text from the current location to the beginning of the query window
- CTRL+SHIFT+END - Select all the text from the current location to the end of the query window
- ALT+F4 - Close the SSMS application
Next Steps
- As you work to improve your productivity as a SQL Server Professional, refer back to this tip to and the first tip in the series.
- Check out these related tips:
- Review the complete list of SSMS Keyboard Shortcuts
- Review SSMS utility on article on msdn.
- Review SQL Server Management Studio Keyboard Shortcuts article on msdn.
- Review other SQL Server Management Studio Tips.
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-06-09
