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.
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 '##%'