Here’s a nifty little SSMS trick that my colleague Tom Hunter educated me on the other day and I thought it was worth sharing. If you’re a keyboard shortcut junkie then you’ll love it.
How often when working with code in SSMS that contains temp tables do you see the following message:
Msg 2714, Level 16, State 6, Line 78
There is already an object named '#table' in the database.
Quite often I would imagine, it happens to me all the time! Usually I write a bit of code at the top of the query window that goes and drops the table if it exists but there’s a much easier way of dealing with it. Remember that temp tables disappear as soon as your sessions ends hence wouldn’t it be nice if there were a quick way of recycling (i.e. stopping and restarting) your session? Well turns out there is and all it takes is a sequence of 4 keystrokes:
- Bring up the context menu using that mythically-named button that usually sits 3 to the right of the space bar
- ‘C’ for “Connection”
- ‘H’ for “Change Connection…”
- ‘Enter’ to select the same connection you had open last time
Once you’ve done it a few times you’ll probably have the whole sequence down to less than a second. Such a simple little trick, I’m annoyed with myself for it not occurring to me before! The only caveat is that you’ll need a “USE <database>” directive at the top of your query window but I don’t think that’s much of a bind!
That is all other than to say if you like little SSMS titbits like this then Lee Everest’s blog is a good one to keep an eye on!