temp table in sysobjects

  • VM-723206

    SSCrazy

    Points: 2964

    Comments posted to this topic are about the item temp table in sysobjects

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Nice question, although a link to a reference would have been nice.

    (ps: quite bizare I answered the QoTD of 22th November on the 20th of November. Time travelling? :-))

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • thecosmictrickster@gmail.com

    SSChampion

    Points: 10386

    Good question. Couple of issues with the construction of it though:

    1. Unless the database context is set to tempdb, or you explicitly reference tempdb..sysobjects, you will get no rows returned.

    2. If you are using the wildcard (%) character, the '=' will need to be changed to 'LIKE'. Again, no rows returned otherwise.



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • TomThomson

    SSC Guru

    Points: 104773

    I agree with Scott, the query which the explanation says willprovide info about the table will in fact retrieve nothing unless "=" is changed to "LIKE" and it's running in the context of tempdb.

    Tom

  • deepak.a

    SSCertifiable

    Points: 5330

    Nice Question .

    There is a another way to Check that , using the below query

    create table #testvm (ID int)

    select * from tempdb.sys.objects where OBJECT_id= object_id('tempDB..#testvm')

    drop table #testvm

    Thanks & Regards

    Deepak.A

  • vk-kirov

    SSCertifiable

    Points: 7686

    select * from sysobjects where name = '#testvm%'

    This query doesn't work and should be replaced with the following:

    select * from tempdb.dbo.sysobjects where name like '#testvm%'

  • Carlo Romagnano

    SSC-Insane

    Points: 21948

    Also this is wrong:

    If you used a query like this, you would see your table:

    You will see ALL tables that begins with '#testvm', also, tables belonging to other sessions.

  • Yousaf Khan

    Ten Centuries

    Points: 1147

    Right This query is not working if the query is running in another DB session

    and also this syntax is wrong

    select * from sysobjects where name = '#testvm%'

    if the query is like

    select * from sysobjects where name like '#testvm%'

    and the session is in tempdb then it will work otherwise it will be like bellow

    select * from tempdb..sysobjects where name like '#testvm%'

    Yousaf Khan

  • vk-kirov

    SSCertifiable

    Points: 7686

    Carlo Romagnano (11/22/2010)


    Also this is wrong:

    If you used a query like this, you would see your table:

    You will see ALL tables that begins with '#testvm', also, tables belonging to other sessions.

    ... only if you are a privileged user on the server (a member of the sysadmin role, I assume). If you are an ordinary user, you can see only the temporary tables which have been created in your current connection.

  • Carlo Romagnano

    SSC-Insane

    Points: 21948

    vk-kirov (11/22/2010)


    Carlo Romagnano (11/22/2010)


    Also this is wrong:

    If you used a query like this, you would see your table:

    You will see ALL tables that begins with '#testvm', also, tables belonging to other sessions.

    ... only if you are a privileged user on the server (a member of the sysadmin role, I assume). If you are an ordinary user, you can see only the temporary tables which have been created in your current connection.

    Any user can view sysobjects in tempdb:

    Open session ONE:

    create table #a(i int)

    select name,id from tempdb.dbo.sysobjects where name like '#a%'

    Open session TWO with same or another user:

    create table #a(i int)

    select name,id from tempdb.dbo.sysobjects where name like '#a%'

    Here the result:

    name id

    #a__________________________________________________________________________________________________________________0000000A6831 86071260

    #a__________________________________________________________________________________________________________________0000000A6855 955646365

  • paul.knibbs

    SSCoach

    Points: 15270

    I got the right answer but for the wrong reason--I thought temporary items wouldn't show up in sysobjects, so I learned something today! 🙂

  • Hardy21

    SSCrazy Eights

    Points: 9708

    Good one point question.

    Below query returns the required result:

    select * from tempdb..sysobjects where name like '#testvm%'

    Thanks

  • vk-kirov

    SSCertifiable

    Points: 7686

    Carlo Romagnano (11/22/2010)


    Any user can view sysobjects in tempdb:

    Open session ONE:

    create table #a(i int)

    select name,id from tempdb.dbo.sysobjects where name like '#a%'

    Open session TWO with same or another user:

    create table #a(i int)

    select name,id from tempdb.dbo.sysobjects where name like '#a%'

    I ran these scripts on a test server (I'm not a sysadmin of that server) and got the following results (underscore characters are truncated).

    Session 1:

    name id

    -------------------------- -----------

    #a____________0000000648F9 756038366

    Session 2:

    name id

    -------------------------- -----------

    #a____________0000000648FA 772038423

    The version of the SQL Server:

    SELECT @@VERSION

    Microsoft SQL Server 2005 - 9.00.4053.00 (Intel IA-64) May 26 2009 14:15:40 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)

  • Carlo Romagnano

    SSC-Insane

    Points: 21948

    vk-kirov

    That's true!

    I ran it on sql2000 sp4.

  • Iulian -207023

    SSCertifiable

    Points: 7509

    Below query returns the required result:

    select * from tempdb..sysobjects where name like '#testvm%'

    Indeed Hardy21 it works, I also tried with:

    select * from tempdb..sysobjects where name like '#testvm'

    and observed it does not work, but then I saw the name column: #testvm__...__00000000000E 🙂

    Thanks,

    Iulian

Viewing 15 posts - 1 through 15 (of 36 total)

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