THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

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:

  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!




Published Thursday, March 18, 2010 9:58 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog


Privacy Statement