THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

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 SQL Saturday #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.  :-)

Published Monday, January 4, 2010 8:01 PM by AaronBertrand

Comments

 

Buck Woody said:

January 4, 2010 7:29 PM
 

Ted Krueger (onpnt) said:

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

January 4, 2010 7:32 PM
 

Rod Colledge said:

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 :-)

January 4, 2010 8:03 PM
 

Brad Schulz said:

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

January 4, 2010 8:13 PM
 

Brad Schulz said:

@Rod:

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

--Brad

January 4, 2010 8:22 PM
 

Rod Colledge said:

@Brad - Now that is just pure evil. I love it :-)

January 4, 2010 8:38 PM
 

Brad Schulz said:

@Rod... BTW, Congrats on MVP award!

January 4, 2010 8:44 PM
 

Tim Ford said:

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.  

January 4, 2010 8:45 PM
 

Aaron Sentell said:

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".

January 4, 2010 9:06 PM
 

cinahcaM madA said:

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

January 4, 2010 9:29 PM
 

Ron Carpenter said:

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

January 4, 2010 9:49 PM
 

Kalen Delaney said:

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!

January 4, 2010 10:54 PM
 

Saggi Neumann said:

(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)

January 5, 2010 3:44 AM
 

jamiet said:

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!

January 5, 2010 4:23 AM
 

Peso said:

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.

January 5, 2010 5:37 AM
 

dan said:

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.

January 5, 2010 5:42 AM
 

Vivekanand said:

January 5, 2010 6:06 AM
 

mjswart said:

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.

January 5, 2010 8:34 AM
 

EBIS said:

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...)

January 5, 2010 10:34 AM
 

cinahcaM madA said:

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.

January 5, 2010 10:52 AM
 

Richb said:

Server groups. Whole enterprise single command shutdown...

January 5, 2010 10:55 AM
 

Peso said:

+1 for the XML schema tip!

January 5, 2010 12:06 PM
 

TiborKaraszi said:

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

January 6, 2010 5:22 AM
 

AmosFiveSix said:

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

January 7, 2010 9:54 AM
 

WhitneyW said:

CTRL + SHIFT + R to refresh the intellisense cache.

January 8, 2010 12:10 AM
 

Madhivanan said:

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

January 8, 2010 2:56 AM
 

EBIS said:

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

January 8, 2010 8:19 AM
 

jerryhung said:

SSMS Tools Pack

http://www.ssmstoolspack.com/

Gotta have (especially if you don't want to pay for Red Gate SQL Prompt)

January 11, 2010 12:45 PM
 

AaronBertrand said:

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).

January 11, 2010 1:42 PM
 

MightyQuin said:

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.

January 19, 2010 12:22 PM
 

Aaron Bertrand said:

Okay, I'm following the lead of Joe Webb ( blog | twitter ), who recently posted " My Most Popular Posts

January 28, 2011 4:09 PM
 

f byron said:

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.

December 19, 2013 11:35 PM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement