Post reply

How can I see temp tables in SQL Server?

  • July 24, 2005 at 11:02 pm

    #90278

    Hi all,

    I am not a sql server admin or developer.I just faced a t-sql code that used temporary tables but I can't see these using information_schema or Enterprise manager or even sysobjects.

    how can I list them?

    -Thanks in advance

  • July 25, 2005 at 12:11 am

    #576658

    Hi,

     you can have it inside TempDb database.

  • July 25, 2005 at 1:17 pm

    #576934

    Hello,

    You also can not see local temp tables that do not belong to your connection. That is why the best way to see them is to run a query in Query Analyzer. To test, open 2 windows in Query Analyzer and run the following

    create table ##mytemptable (i int)    --Run this in the first window

    create table #mytemptable (i int)      --Run this in the second window

    Then in the third window run:

    Use tempdb

    select * from sysobjects

    where xtype = 'u'

    It will return a list of your temp tables:

    #mytemptable________________________________________________________________________________________________________000000002331

    ##mytemptable

    Pay attention to the underscores and number after the name #mytemptable. This is a connection-specific suffix that SQL Server adds to distinguish between tables of the same name from different connections.

    Yelena

    Regards,Yelena Varsha

  • July 26, 2005 at 1:50 am

    #577080

    Anyhow, don't forget that a temp table "local" to the session who creates the temp table.

    That's why you can create temp tables with the same name in different sessions (like for example you can create a temp table in a stored proc and then execute that SP in paralell from several client apps. )



    Bye
    Gabor

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply