Quick poll : what is your favorite Management Studio tip or trick?

I am putting together a presentation on SSMS Tips & Tricks, which I will debut at SQLSaturday #34.  I already have a pretty full slide deck, but I am not the be-all and end-all of SSMS, so in the interest of distributing this information as widely as possible, I would be interested to see if you have any tips and/or tricks that I haven't included.  I will give credit if I use any submissions of course, though if you are going to suggest I turn IntelliSense off, I've already heard that one dozens of times.  🙂

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at SQLPerformance and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

31 Responses

  1. f byron says:

    i know this is an old thread, but just learned a life-changing one the other day and i didn't see it here so figured i'd share.
    let's say you're writing something long and you get an error.
    double-click on the red error message displayed in the messages tab and it will take you directly to (and highlight) the offending line in the query editor window.  no more using line numbers, accounting for whitespace, multiple batches, etc. etc. etc.

  2. MightyQuin says:

    My favourite "how did you do that?" effect is from assigning the "Change Connection" button to a hot key.
    (Right click on the tool bar, and select customise.  Right click again on the "change Connection …" icon.  Change the style to "image and Text" and place an ampersand (&) in the "Name" box before the letter you want to assign to the shortcut … ideally one that isn't already assigned (unlike the default "h"!), such as the "g", then close the customize window)
    Now, as if by magic, ALT-G pops up a "Connect to database" window to change the connection of the current.
    That's closely followed by using ":CONNECT Server" in SQLCmd mode to run against a different server.

  3. AaronBertrand says:

    Jerry, just to make sure we don't set incorrect expectations, you're referring to the reformatting and snippets features, but not that SSMS Tools Pack has a replacement for IntelliSense (since that is what SQL Prompt is most famous for).

  4. jerryhung says:

    SSMS Tools Pack
    http://www.ssmstoolspack.com/
    Gotta have (especially if you don't want to pay for Red Gate SQL Prompt)

  5. EBIS says:

    When in the Showplan-Window, you can with Ctrl-Shift-Numpad+ zoom in and with Ctrl-Shift-Numpad- zoom out the plan

  6. Madhivanan says:

    ALT+F1 to view definition of the table (same as sp_help 'table')

  7. WhitneyW says:

    CTRL + SHIFT + R to refresh the intellisense cache.

  8. AmosFiveSix says:

    CTRL-R for showing and hiding the results pane.
    Posted my favorite tricks here: http://www.amosfivesix.com/sql/37-sql-server-management-studio-tricks

  9. TiborKaraszi says:

    I don't like being thrown into the most recently connected database when I start SSMS (the login dialog which by default is shown when you start SSMS). So I have a shortcur with something like below. Makes me always go to the same instance:
    "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -S TK\a -d tempdb -E -nosplash

  10. Peso says:

    +1 for the XML schema tip!

  11. Richb says:

    Server groups. Whole enterprise single command shutdown…

  12. cinahcaM madA says:

    EBIS: Nice tip! I've been saving my XML out to files, then using Visual Studio to do that work. Great to know that I can skip a step.

  13. EBIS says:

    Creating a XML-Schema from a XML-Result…
    (When xml_Result opened in own window, then is in the main menu an addtional XML-Entry…)

  14. mjswart says:

    Just changed my batch separator to ENGAGE. (I also mapped "EN" and "GO" sql snippets using SSMS tools).
    Also interesting is that no matter what the batch separator is, it still takes an integer argument. So that hasn't changed.

  15. dan says:

    Nice thread, learnt several things !
    Options/Query Results/SQL Server/Results To Grid/Display results in a separate tab/Switch to results tab after the query executes.
    F5 to run a query.
    F6/Shift+F6 to cycle between tabs for a given query.
    I find keyboard control MUCH quicker.
    Highlight any table or view name in t-sql and hit Alt+F1 for sp_help and a list of its fields. Obviously, you can add more via keyboard shortcuts but this one is great while writing sp's when you can't remember a field name.

  16. Peso says:

    Run code against a server group.
    Select a server group icon, click new query and execute the code. The code is run against all servers in the server group.

  17. jamiet says:

    Simple but cool, the "Trace query in Profiler" feature: http://www.texastoo.com/post/2009/11/24/More-SQL-Server-Management-Studio-2008-features.aspx
    I disagree with Ted though (sorry Ted), I *hate* the colourization of windows. Why? Because they only work for queries that are launched from the Registered Servers window, not from Object Explorer and hence the colourization can give wrong information (e.g. make you think you're not querying production when in fact you are). They're dangerous! Use Mladen's SSMS Tools addin instead!!!
    I quite like the capture Client Statistics feature even though I always forget that its there!!!!
    CTRL-F4 for closing query windows. Don't know how I coped before I knew about that!

  18. Saggi Neumann says:

    (SHIFT-)CTRL-F6 rules!
    It's so much better than (SHIFT-)CTRL-TAB  – it's actually what CTRL-TAB did on query analyzer…
    Also 'copy with headers' (the context menu option in 2008, or the setting for SSMS in 2005)

  19. Kalen Delaney says:

    Hi Aaron
    I never thought of the ability to change the batch separator as an SSMS feature; it's been available in every TSQL interface including the way way old isql …. but I love the idea of using 'ENGAGE'… fewer characters than 'MAKE IT SO'
    My favorite as-yet-unmentioned SSMS tidbit is the ability to change the keyboard to use SQL 2000 shortcut keys, and also the ability to change font sizes, colors, etc.
    And I just added -nosplash to my startup shortcut… way cool!

  20. Ron Carpenter says:

    Templates. I create custom templates for a lot of my routine tasks, and just drag and drop into the current window.

  21. cinahcaM madA says:

    The fact that SSMS has a built-in web browser, with instant "boss key" (CTRL-TAB) 🙂

  22. Aaron Sentell says:

    SSMS Tools Pack. It's an add-on to SSMS that adds a variety of features not found in the base install of Management Studio. I find the connection coloring to be better than SSMS 2008's, and the ability to look at SQL execution history even in tabs that have been closed is invaluable. And there are many other useful tools in this "swiss army knife".

  23. Tim Ford says:

    The ability to turn off the splash screen that typically loads when you launch SSMS from a shortcut.  Adding -nosplash in the shortcut launches SSMS much quicker as a result.  

  24. Brad Schulz says:

    @Rod… BTW, Congrats on MVP award!

  25. Rod Colledge says:

    @Brad – Now that is just pure evil. I love it 🙂

  26. Brad Schulz says:

    @Rod:
    A cute trick to play on someone is to secretly change their batch separator to "SELECT".  Sit back and watch the fun.
    –Brad

  27. Brad Schulz says:

    Two of my favorite shortcuts:
    CTRL+U to change the current database context (simply activates the database selection combobox)
    CTRL+I to do an incremental search (rather than using CTRL+F)
    I also like the following (which you're able to do in several Microsoft and/or Windows-related programs):  Hold down the ALT key while highlighting information so you can select columnar information and not just whole rows.  For example, in SSMS if you want to un-indent several lines of code, highlight the white space at the beginning of the lines (using ALT+Drag) and then hit the DELETE key.
    –Brad

  28. Rod Colledge says:

    I always enjoy customizing/changing the batch separator from "GO" to something else like "ENGAGE". I think Greg Low showed me that trick years ago … Freaks people out who don't know about it 🙂

  29. Ted Krueger (onpnt) says:

    SSMS 2008, ability to launch activity monitor now on startup.
    Tools–>Options.  Drop drop "At Startup" and select "open object explorer and activity monitor"
    For a DBA this is handy and keeps you from a few clicks for a quick view of the resources.
    Adding custom templates is always a great tip.  Commonly run DMV queries, common db work specific to your servers etc.. all links there in ssms
    Colorized windows for registered servers.  dev, test, uat and prod all with a color bar so you have a quick view of where you are.  we've all done it!  executed in prod while you thought you were in dev 😉
    Maintenance plan editor inside ssms is always a tip some don't know and go to bids for.  fully functional with most tasks that can accomplish 'most' tasks.
    moving tab around.  might not notice you can do this but you can format tabs horizontal or vertical so you can watch executing query windows while working in another.  just drag the tabs over each other and the selection comes up
    I'll post back more.  those are the few off the top of my head