Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by VM » temp table in sysobjects
Add to briefcase
36 posts, Page 1 of 4
1234»»»

temp table in sysobjects Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
VM-723206
Posted Saturday, November 20, 2010 12:30 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Saturday, April 06, 2013 12:20 AM
Points: 649, Visits: 263
Comments posted to this topic are about the item temp table in sysobjects
Post #1023959
Koen Verbeeck
Posted Saturday, November 20, 2010 12:31 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 9,376, Visits: 6,472
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? )


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1023961
The CosmicTrickster
Posted Sunday, November 21, 2010 12:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 2,605, Visits: 760
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
Post #1024069
L' Eomot Inversé
Posted Sunday, November 21, 2010 1:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:11 PM
Points: 7,104, Visits: 7,168
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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #1024071
deepak.a
Posted Monday, November 22, 2010 12:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 13, 2012 10:04 AM
Points: 554, Visits: 861
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
Post #1024160
vk-kirov
Posted Monday, November 22, 2010 12:48 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 3,191, Visits: 4,149 
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%'
Post #1024169
Carlo Romagnano
Posted Monday, November 22, 2010 1:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:51 AM
Points: 1,972, Visits: 1,821
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.
Post #1024174
Yousaf Khan
Posted Monday, November 22, 2010 1:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 26, 2012 10:43 PM
Points: 407, Visits: 73
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
Post #1024189
vk-kirov
Posted Monday, November 22, 2010 1:42 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 3,191, Visits: 4,149
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.
Post #1024191
Carlo Romagnano
Posted Monday, November 22, 2010 2:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:51 AM
Points: 1,972, Visits: 1,821
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
Post #1024196
« Prev Topic | Next Topic »

Add to briefcase
36 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.