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

 

Skip to main content