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:

1. Bring up the context menu using that mythically-named button that usually sits 3 to the right of the space bar
2. ‘C’ for “Connection”
3. ‘H’ for “Change Connection…”
4. ‘Enter’ to select the same connection you had open last time

(screenshots below)

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!

@jamiet