temp table in sysobjects

Question

Post reply

temp table in sysobjects

VM-723206

SSCrazy

Points: 2964
More actions
November 20, 2010 at 12:30 pm

#226905

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

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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: 22670 More actions · Answer 6

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: 1149 More actions · Answer 7

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 More actions · Answer 8

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: 22670 More actions · Answer 9

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

paul.knibbs SSCoach Points: 15320 More actions · Answer 10

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 More actions · Answer 11

Good one point question.

Below query returns the required result:

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

Thanks

vk-kirov SSCertifiable Points: 7686 More actions · Answer 12

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: 22670 More actions · Answer 13

Carlo Romagnano

SSC-Insane

Points: 22670

November 22, 2010 at 3:21 am

#1253639

vk-kirov

That's true!

I ran it on sql2000 sp4.

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 14

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