This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

Kill your temp tables using keyboard shortcuts : SSMS

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

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