Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

SSMS keyboard shortcuts (Part 2 of 2)

MSSQLTips author Arshad Ali By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | More > SQL Server Management Studio
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:

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
Next Steps


Last Update: 6/9/2011


About the author
MSSQLTips author Arshad Ali
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, June 15, 2011 - 8:08:11 AM - Franky L. Read The Tip

Thank you for this series of articles. Very useful.

Is there a shortcut for refreshing the Object Explorer?

When creating new database objects, like views, it is handy to refresh the object explorer.

Franky


Wednesday, June 15, 2011 - 8:57:46 AM - Arshad Read The Tip

Thanks Franky,

You can use UP and DOWN arrow to navigate in Object Explorer and RIGHT and LEFT arrow to expand and collapse the node. To refresh the objects beneath a node you can hit F5 on that node.

Hope it helps.


Wednesday, June 15, 2011 - 9:09:40 AM - Franky L. Read The Tip

Hi Arshad,

Great that works ! Thx.


Wednesday, June 15, 2011 - 11:08:46 AM - Erwin Oosterhoorn Read The Tip

Hi,

Nice to see all the shortcuts explained with some examples. There is one (maybe more) that I use a lot and have not seen:

Ctr + E to run the (highlighted) script. F5 is sometimes too much of a stretch.

Thanks,

Erwin


Wednesday, August 21, 2013 - 11:49:27 PM - vijay Read The Tip

is there any shortcut for shortcut key for Script Database as Select To in SQL Server ?



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.