Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
2  
What RDBMS are you using? (DB2, Oracle and Teradata all use them) –  FreshPhilOfSO 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
add comment

1 Answer

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. –  FreshPhilOfSO Jan 28 '13 at 18:20
    
I knew it would be simple. Thanks Mike! –  codehammer Jan 28 '13 at 18:36
add comment

Your Answer

 
discard

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.