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.

  • 2
    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
  • 3
    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

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 '##%'
  • See my comment on the question. – Philᵀᴹ Jan 28 '13 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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