The algorithm for generating the temp table name suffix changed between Shiloh in Yukon. In any case, it is not based upon the session id.
I suggest you give your temp table unique prefixes and do this:
use tempdbgo
select * from INFORMATION_SCHEMA.TABLESwhere TABLE_CATALOG = 'tempdb'and TABLE_SCHEMA = USERand TABLE_NAME like '#DIRECTORY%'go
Note that TABLE_SCHEMA = USER only works in Shiloh. Reason - because of the user/schema separation feature. In Yukon, the TABLE_SCHEMA is really that ... the table's schema name... which might not be the same as the user name. We have real schemas now. User X can own schemas Y and Z. All schema names occupy the same namespace regardless of owner, however.
Another difference between Shiloh and Yukon is this: You cannot use 3-part names to refer to tempdb from another database context unless you are sa. You must "use" tempdb and stick to a 2-part name, as shown in the example above. This works in Yukon, however, for non-sa users.
In Summary
For Shiloh
TABLE_SCHEMA = user name
This won’t work from non-tempdb calling context unless you’re sa/dbo. You get an empty set back.
use otherdbgo
select * from tempdb.INFORMATION_SCHEMA.TABLES go
The temp table name is formed from login time stamp + nest level.
For Yukon
TABLE_SCHEMA = schema name
This will work from non-tempdb calling context even if you are a least-privileged user. You get the rows back.
use otherdbgo
select * from tempdb.INFORMATION_SCHEMA.TABLES go
Formed from an internal counter.
If you want to write code that works both on Shiloh and Yukon for non-sa users, then:
a) You must "use tempdb"b) You must use 2-part name: SELECT * FROM INFORMATION_SCHEMA.TABLESc) You must assume that for Yukon customers, the schema name == user name. This will be the case for all upgraded databases. This will also hold true as long as your customers avoid user/schema separation features. This will hold true for the old “sp_adduser” API. d) You can enforce (c) this by using DDL triggers in Yukon and doing ROLLBACKs on CREATE SCHEMA and CREATE USER statements.
