Use of INFORMATION_SCHEMA views to access temp tables.

  • Article

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