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. 🙂
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.
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.
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).
SSMS Tools Pack
Gotta have (especially if you don't want to pay for Red Gate SQL Prompt)
When in the Showplan-Window, you can with Ctrl-Shift-Numpad+ zoom in and with Ctrl-Shift-Numpad- zoom out the plan
ALT+F1 to view definition of the table (same as sp_help 'table')
CTRL + SHIFT + R to refresh the intellisense cache.
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
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
+1 for the XML schema tip!
Server groups. Whole enterprise single command shutdown…
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.
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…)
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.
You can easily Identify your SQL Enviroments by colours
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.
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.
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!
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)
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!
Templates. I create custom templates for a lot of my routine tasks, and just drag and drop into the current window.
The fact that SSMS has a built-in web browser, with instant "boss key" (CTRL-TAB) 🙂
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".
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.
@Rod… BTW, Congrats on MVP award!
@Brad – Now that is just pure evil. I love it 🙂
A cute trick to play on someone is to secretly change their batch separator to "SELECT". Sit back and watch the fun.
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.
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 🙂
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
Of course the customizable columns in the new Object Explorer Details (OED) pane: http://blogs.msdn.com/buckwoody/archive/2008/09/03/the-oed-details-area.aspx
Many more tips here: http://blogs.msdn.com/buckwoody/archive/2009/07/23/sql-server-management-studio-tips-and-tricks.aspx