Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am trying to get a list of the global temporary tables that are currently active. There seems to be an issue with them bumping in to each other (same name). As the 'acting' dba, trying to sort out a mess here.

share|improve this question
What RDBMS are you using? (DB2, Oracle and Teradata all use them) – Phil Jan 28 '13 at 18:19
Sorry, SQL Server 2005. – codehammer Jan 28 '13 at 18:28
They're bumping into each other because that's precisely what global temporary tables do - hence the name global. I have found very few practical uses for them myself. – Aaron Bertrand Jan 28 '13 at 18:46
I agree with @AaronBertrand on this topic. There has to be a very strict and explicit requirement for global temp tables. – Thomas Stringer Jan 28 '13 at 18:48
I am only trying to patch up an already patched up system. Duct tape over bandaids. – codehammer Jan 29 '13 at 19:38
up vote 5 down vote accepted

For SQL Server 2005+, all global temp tables (tables that are created with a ## prefix) will be found in tempdb. This should work for you:

SELECT * FROM tempdb.sys.tables
WHERE name LIKE '##%'
share|improve this answer
See my comment on the question. – Phil Jan 28 '13 at 18:20
I knew it would be simple. Thanks Mike! – codehammer Jan 28 '13 at 18:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.