Use of INFORMATION_SCHEMA views to access temp tables.


An MVP recently sent us an email asking how to use the INFORMATION_SCHEMA views to access temp tables.  This MVP thought the session ID (spid) was needed to construct the suffix.  Here was our response:


=========================


Hi,


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 tempdb
go


select *
from   INFORMATION_SCHEMA.TABLES
where  TABLE_CATALOG = ‘tempdb’
and    TABLE_SCHEMA  = USER
and    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 otherdb
go


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 otherdb
go


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.TABLES
c) 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.


Clifford  Dibble
Program Manager, SQL Server Engine