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 2 of 4
««1234»»»

temp table in sysobjects Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
paul.knibbs
Posted Monday, November 22, 2010 2:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,272, Visits: 4,309
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!
Post #1024201
Hardy21
Posted Monday, November 22, 2010 2:29 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 7:11 AM
Points: 877, Visits: 1,159
Good one point question.

Below query returns the required result:
select * from tempdb..sysobjects where name like '#testvm%'

Thanks
Post #1024204
vk-kirov
Posted Monday, November 22, 2010 2:58 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: Thursday, June 13, 2013 8:50 PM
Points: 3,208, Visits: 4,178
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)
Post #1024209
Carlo Romagnano
Posted Monday, November 22, 2010 3:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,996, Visits: 1,864
vk-kirov

That's true!
I ran it on sql2000 sp4.
Post #1024214
Iulian -207023
Posted Monday, November 22, 2010 3:37 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 13, 2013 6:47 AM
Points: 847, Visits: 770

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
Post #1024219
Randhir Singh
Posted Monday, November 22, 2010 4:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 9:46 PM
Points: 91, Visits: 328
Good question!!!

I just want to add few lines here..

1. sysobjects is a view not table
2. We can list all database objects using this view but for current database, if we want to see database objects then we need to add reference.

Like Select * From tempdb..sysobjects

3. Adding a filter xType ='U', list all temp tables to tempdb database.
4. We can also use "Type" column instead of "xType", both are same but Type is for backward compatibility.

------------
Randhir Singh
Post #1024233
Hugo Kornelis
Posted Monday, November 22, 2010 4:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:45 AM
Points: 5,296, Visits: 7,238
Randhir Singh (11/22/2010)
Good question!!!

I just want to add few lines here..

1. sysobjects is a view not table

And a deprecated one to boot. New code should use the new system views. In this case, sys.objects.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1024242
RichardDouglas
Posted Monday, November 22, 2010 5:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 13, 2013 3:50 AM
Points: 969, Visits: 652
Good question, thanks for taking the time to help educate the SQL community.

Hope this helps,
Rich
Post #1024248
WayneS
Posted Monday, November 22, 2010 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 6,386, Visits: 8,288
BTW, the best way to check for the existance of a temporary table in your current session is:

IF OBJECT_ID('tempdb..#YourTempTable') IS NOT NULL

Note that in SQL Server thru version 2008R2, temp tables are created with a positive object_id value, so you might want to use:
IF OBJECT_ID('tempdb..#YourTempTable') > 0

However, the next version of SQL (code named Denali) is returning an object_id with a negative value, so the >0 won't work there, and it will need to be recoded.

Every other method I've seen for checking for the existence of a temp table fails when the temp table is created in a different sessions - those methods either crash, or return a positive (yes, it exists) incorrectly.

Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1024274
Mike Is Here
Posted Monday, November 22, 2010 6:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 1,355, Visits: 436
Correct sql is 

select * from tempdb..sysobjects where name like '%#testvm%'
Post #1024284
« Prev Topic | Next Topic »

Add to briefcase
36 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

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