Tuesday, 7 February 2012

SSMS keyboard shortcuts

SSMS keyboard shortcuts



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.


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:
sql server management studio intellisense
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:
ssms shortcut ctrl+shift+space
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.
use the auto complete feature of intellisense
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:
refreshing the local cache in ssms

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.
execute scripts in sql sever management studio
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.
changing the results display

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).
query execution plans in ssms
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.
review the query execution plan with the query execution 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:
ssms debugging
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:
ssms has breakpoint window
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:
a long script in the ssms query window might appear small
FFor 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.
press alt+f1 to display the meta information
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


Post a Comment