Kill your temp tables using keyboard shortcuts : SSMS

Posted by jamiet on SQL Blog See other posts from SQL Blog or by jamiet
Published on Thu, 18 Mar 2010 19:58:09 GMT Indexed on 2010/03/18 20:02 UTC
Read the original article Hit count: 590

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!




© SQL Blog or respective owner

Related posts about keyboard shortcuts

Related posts about SQL Server