8

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.

Improve this question
5
  • 2
    What RDBMS are you using? (DB2, Oracle and Teradata all use them)
    – Philᵀᴹ
    Jan 28, 2013 at 18:19
  • Sorry, SQL Server 2005.
    – codehammer
    Jan 28, 2013 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, 2013 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, 2013 at 18:48
  • I am only trying to patch up an already patched up system. Duct tape over bandaids.
    – codehammer
    Jan 29, 2013 at 19:38

1 Answer 1

Reset to default
12

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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