Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Internal storage of temporary table names

Feb 21 2011 5:19AM by Madhivanan   

Someone in the forums asked about the internal storage of the temporary table. The questioner was complaining that the table was not stored in the database

Let us consider this example

create table #t(i int)

Now check the existance of the table in information_schema.tables view

select * from tempdb.information_schema.tables
where table_name='#t'

The above query does not return any informations about the table #t. Now execute sp_help and see what happens

exec tempdb..sp_help '#t'

It returns multiple resultsets. The first resultset contains the table name. If you see the table name it is as follows


SQL server appends lot of underscores and some id as part of the temporary table name. Do you know why this is happening?. Ok. Now open another query analyser and run this code.

create table #t(i int)

Now execute sp_help and see what happens


So from the above examples it is clear that the actual name is stored differently. It is because that anyone can create temporary table with the same table name and in order to avoid any conflict on the table name, SQL Server stores the name in a unique way. So if you want to know the structure of the temporary table always make use of sp_help system stored procedure that runs on the tempdb database.

Tags: t-sql, sql_server, tempdb, sqlserver, tsql, temporary_table, BRH, #TSQL, #SQLServer,

2 · 40% · 13039



  • In fact, you can "simply" use the Object_ID() function to find the relevant records in the meta-data tables in tempdb. Just make sure to prefix the #table if you're not in tempdb

    e.g.: CREATE TABLE #t (a int)

    SELECT * FROM tempdb.sys.tables WHERE objectid = ObjectId('tempdb..#t')

    commented on Feb 24 2011 5:19PM
    Roby Van Hoye
    224 · 1% · 210
  • Nice tip Madhivanan and Roby. I still believe that uniqueness can be ensured with out those underscore characters. They already have a serial number at the end which can be unique by itself.

    commented on Mar 9 2011 7:25AM
    Jacob Sebastian
    1 · 100% · 32235
  • The important part is that exec tempdb..sphelp '#t' is session specific. If we run in 2 sessions create table #t(i int) exec tempdb..sphelp '#t' we'll get 2 different results. Something similar to #t________0000000000AB in one session and #t________0000000000AC in other one. But if we query select tableName from tempdb.informationschema.tables where tablename like'#t%' we'll get in both sessions the same result sumilar to #t________0000000000AB #t________0000000000AC And if we want to store name of the temptable in a variable we should use declare @tname sysname SELECT @tname=name FROM tempdb.sys.tables WHERE objectid = ObjectId('tempdb..#t') select @tname and it will be session specific.

    commented on Mar 9 2011 2:01PM
    Leonid Koyfman
    50 · 4% · 1183
  • Jacob, the datatype of the column name is sysname (nvarchar(128). So SQL Server fills underscores to have the total length 128

    commented on Mar 10 2011 9:11AM
    2 · 40% · 13039
  • Thanks Leonid Koyfman for the feedback

    commented on Mar 10 2011 9:12AM
    2 · 40% · 13039
  • Thank-you, Madhivanan & Roby! We always use OBJECT_ID property for all of our tables (temporary or permanent) as a best practice.

    commented on Mar 12 2011 9:52AM
    Nakul Vachhrajani
    4 · 36% · 11648

Your Comment

Sign Up or Login to post a comment.

"Internal storage of temporary table names" rated 5 out of 5 by 2 readers
Internal storage of temporary table names , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]