print versionprint version  
Sybase ASE
All versions
Last updated: 12 February 2006
Reading temporary tables that aren't yours
 
Temporary tables, having a name starting with the "#" character, are a well-known feature of Adaptive Server Enterprise. Temporary tables are special in that they are session-specific: a session cannot access another session's temporary table, not even when having sa_role.
This is normally fine, but sometimes it could be interesting if this were possible: one practical case involved a runaway application which kept a "scratchpad" of data in a temporary table. After reading the contents of this table using the procedure described below, the reason for the application's behaviour could be found and corrected.

A temporary table created as #mytable is actually stored in tempdb under a different name, like #mytable_____00000090019545666. Though not formally documented or supported, this name contains some useful information: characters 14-15 indicate the nesting level at which the table was created, while characters 16-20 are the "spid" of the table owner process. The latter is especially useful when a huge temporary table is filling up tempdb: the owner process can now be identified (spid 9 in this example) and possibly killed. The remaining characters 21-30 don't contain any useful information (well... read this).

While a temporary table's structure and size can be found by any user by querying the tempdb system tables, only the table owner can select from the table directly (although in older versions of SQL Server, this was in fact possible by using some low-level tricks). For completeness, it should be noted that one could dump the table's data pages using the dbcc page command; however, this requires manual decoding of binary row data which is usually not feasible.

Because a temporary table owned by a different session also cannot be renamed, the only way to access it is as follows:
  1. Make a database dump of tempdb; after all, tempdb is in many ways a normal database.

  2. Load this dump into another database, say, "newdb"

  3. Manually update the table name, for example:
     update newdb..sysobjects 
     set name = "mytable2"
     where name =  "#mytable_____00000090019545666"
        
  1. Now, select * from newdb..mytable2 will show the original table’s contents at the moment when the database dump was made.


Author: Rob Verschoor (rob@sypron.nl)

Sybase, Inc. This article above first appeared in the 4th Quarter Issue 1998 of the ISUG Technical Journal.





Update 1 (August 2005):
The last 10 characters of a temporary table name actually do contain some information that may be useful: these digits represent the amount of time (in units of 1/300th of a second) since midnight on the day the owner session connected to ASE.
Although formally undocumented and unsupported, try this query in ASE pre-15:
create table #t (a int)

select 
  name,
  convert(varchar,
     dateadd(ss, convert(int,substring(name,21,10))/300, 
	 left(convert(varchar,getdate(),100),11))
  ,109)
from tempdb..sysobjects 
where name like "#%"
Note the implicit assumption that the owner session connected to ASE today: we have no way of telling from this information alone whether that is true (actually, in ASE 12.5 and 15, we can get this information in a much simpler way from sysprocesses.loggedindatetime).

(Thanks to Bret Halford for figuring this out).


Update 2 (August 2005):
As of ASE 12.5.0.3, ASE can have multiple temporary databases. A temporary table is created in the session's temporary database, so this may be a different database than tempdb. For the sake of simplicity, we'll stick with tempdb for the examples here.


Update 3 (September 2005):
In ASE 15, the internal name of a temporary table has changed. This is because object names can be 255 bytes long in ASE 15 -- and the ASE designers didn't want to add another 200 underscores to most temporary table names:
create table #t (a int)

in pre-15:	#t___________00000150008240896
in 15.0:    #t00000150003699485

There are hardly any practical implications. You'll only need to use a different algorithm to extract the different elements from the table name:
-- ASE pre-15
select name,
       owner_spid = substring(name, 16, 5),
       nestlevel  = substring(name, 14, 2),
       loggedintime = 
         convert(varchar,
           dateadd(ss, convert(int,substring(name,21,10))/300, 
           left(convert(varchar,getdate(),100),11))
         ,109)
from tempdb..sysobjects 
where name like "#%"

-- ASE 15
select name,
       owner_spid = substring(right(name,17), 3, 5),
       nestlevel  = substring(right(name,17), 1, 2)
from tempdb..sysobjects 
where name like "#%"

As a result of the change in the internal name format in ASE 15, names of temporary tables can now contain more than 13 significant characters. The only practical implication worth mentioning is shown in the following lines:
    select * from #t12345678901
    select * from #t1234567890123456
In ASE 12.5, these statements refer to same table #t12345678901 since characters in the table name beyond the first 13 are simply ignored. In ASE 15 however, these statements refer to two different tables. Only when you have any SQL code containing such a 'mistake', it will fail in ASE 15 since the second table doesn't exist...


 
 This document is located at www.sypron.nl/temptab.html