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 Monday, November 22, 2010 2:14 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 1,607, Visits: 5,469
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
Posted Monday, November 22, 2010 2:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 1,012, Visits: 1,282
Good one point question.

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



Thanks
Post #1024204
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: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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
Posted Monday, November 22, 2010 3:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:24 AM
Points: 2,451, Visits: 2,342
vk-kirov

That's true!
I ran it on sql2000 sp4.
Post #1024214
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: Today @ 1:33 AM
Points: 904, Visits: 837

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
Posted Monday, November 22, 2010 4:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 2, 2014 4:04 AM
Points: 100, Visits: 339
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
Posted Monday, November 22, 2010 4:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 5,915, Visits: 8,162
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
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: Friday, June 27, 2014 2:20 AM
Points: 974, Visits: 691
Good question, thanks for taking the time to help educate the SQL community.

Hope this helps,
Rich



Post #1024248
Posted Monday, November 22, 2010 6:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:16 AM
Points: 6,580, Visits: 8,855
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
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: Monday, July 7, 2014 10:36 AM
Points: 1,393, Visits: 476
Correct sql is

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

Post #1024284
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse