SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


temp table in sysobjects


temp table in sysobjects

Author
Message
paul.knibbs
paul.knibbs
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7936 Visits: 6240
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
Hardy21
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5556 Visits: 1399
Good one point question.

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



Thanks
vk-kirov
vk-kirov
SSCertifiable
SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)SSCertifiable (5.6K reputation)

Group: General Forum Members
Points: 5638 Visits: 4408
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
Carlo Romagnano
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12336 Visits: 3517
vk-kirov

That's true!
I ran it on sql2000 sp4.
Iulian -207023
Iulian -207023
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4337 Visits: 1248

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
Randhir Singh
Randhir Singh
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 364
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
Hugo Kornelis
Hugo Kornelis
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34314 Visits: 13113
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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
RichardDouglas
RichardDouglas
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2261 Visits: 707
Good question, thanks for taking the time to help educate the SQL community.

Hope this helps,
Rich



WayneS
WayneS
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: General Forum Members
Points: 49122 Visits: 10850
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
Author - SQL Server T-SQL Recipes
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

Mike Is Here
Mike Is Here
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2414 Visits: 517
Correct sql is

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search