Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

temp table in sysobjects Expand / Collapse
Author
Message
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: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Comments posted to this topic are about the item temp table in sysobjects
Post #1023959
Posted Saturday, November 20, 2010 12:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 13,357, Visits: 10,222
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
Posted Sunday, November 21, 2010 12:56 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 5:05 PM
Points: 2,644, Visits: 824
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
Posted Sunday, November 21, 2010 1:19 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:18 AM
Points: 8,573, Visits: 9,082
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
Post #1024071
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: Thursday, January 2, 2014 9:57 AM
Points: 554, Visits: 863
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
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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Posted Monday, November 22, 2010 1:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 2,469, Visits: 2,346
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
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: Saturday, June 21, 2014 1:10 AM
Points: 407, Visits: 76
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
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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Posted Monday, November 22, 2010 2:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:33 AM
Points: 2,469, Visits: 2,346
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 1234»»»

Permissions Expand / Collapse