Post reply

temp table in sysobjects

  • November 20, 2010 at 12:30 pm

    #226905

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

  • November 20, 2010 at 12:31 pm

    #1253432

    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

  • November 21, 2010 at 12:56 pm

    #1253516

    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

  • November 21, 2010 at 1:19 pm

    #1253518

    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

  • November 22, 2010 at 12:17 am

    #1253594

    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

  • November 22, 2010 at 12:48 am

    #1253599

    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%'

  • November 22, 2010 at 1:06 am

    #1253604

    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.

  • November 22, 2010 at 1:41 am

    #1253618

    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

  • November 22, 2010 at 1:42 am

    #1253620

    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.

  • November 22, 2010 at 2:05 am

    #1253624

    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:

    nameid

    #a__________________________________________________________________________________________________________________0000000A683186071260

    #a__________________________________________________________________________________________________________________0000000A6855955646365

  • November 22, 2010 at 2:14 am

    #1253629

    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! 🙂

  • November 22, 2010 at 2:29 am

    #1253631

    Good one point question.

    Below query returns the required result:

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

    Thanks

  • November 22, 2010 at 2:58 am

    #1253636

    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)

  • November 22, 2010 at 3:21 am

    #1253639

    vk-kirov

    That's true!

    I ran it on sql2000 sp4.

  • November 22, 2010 at 3:37 am

    #1253644

    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 35 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy