Tell me more ×
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.
up vote 2 down vote favorite

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) – Phil Jan 28 at 18:19
Sorry, SQL Server 2005. – codehammer Jan 28 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 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 at 18:48
I am only trying to patch up an already patched up system. Duct tape over bandaids. – codehammer Jan 29 at 19:38

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

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.