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


temp table in sysobjects


temp table in sysobjects

Author
Message
nigel.
nigel.
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3231 Visits: 2907
mbova407 (11/22/2010)
Correct sql is

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



Not quite.

It was mentioned in an earlier post that using LIKE and wildcards may return more than one record if another temporary table is created with a similar name.
And in another post using the built-in function OBJECT_ID was proposed.
Then Hugo brought up the fact that sysobjects is deprecated and sys.objects shouild be used instead.

So putting all this together we have:
select * from tempdb.sys.objects where object_id = OBJECT_ID('tempdb.dbo.#testvm')



Hope I've not missed anything :-)

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7495 Visits: 3395
WayneS (11/22/2010)
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




You can test <> 0
I always use
IF OBJECT_ID('tempdb..#YourTempTable') <> 0

:-D

I run on tuttopodismo
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7495 Visits: 3395
Carlo Romagnano (11/22/2010)
WayneS (11/22/2010)
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




You can test <> 0
I always use
IF OBJECT_ID('tempdb..#YourTempTable') <>  0

:-D


I run on tuttopodismo
Geoff A
Geoff A
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: 2442 Visits: 1808
So putting all this together we have:
select * from tempdb.sys.objects where object_id = OBJECT_ID('tempdb.dbo.#testvm')



Hope I've not missed anything :-)


seems perfect to me....
sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4012 Visits: 2924
Carlo Romagnano (11/22/2010)
Carlo Romagnano (11/22/2010)
WayneS (11/22/2010)
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




You can test <> 0
I always use
IF OBJECT_ID('tempdb..#YourTempTable') <>  0

:-D


But as Wayne said, the best way is to use IS NOT NULL. Why? Because then you're testing for the defined behavior. OBJECT_ID() is designed to return NULL (NOT 0) for objects which do not exist or are not schema-scoped.

So if you test against 0, you're designing potential failure (there's no technical reason why Microsoft couldn't have SQL Server create an object with an ID of 0 -- it's just another number, after all) into your application for no good reason.

http://msdn.microsoft.com/en-us/library/ms190328.aspx
Seth Kramer
Seth Kramer
Old Hand
Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)Old Hand (301 reputation)

Group: General Forum Members
Points: 301 Visits: 184
Okay but where does the numbering at the end of the underscores correspond to the spid that created it?

Hypothetically, lets say I wanted to see what spid owned each copy of a local temp table. How would I do that? Also does it work for a global temp table.
WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21534 Visits: 10652
mbova407 (11/22/2010)
Correct sql is

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



Try this:
in one query window, run:
CREATE TABLE #testvm (RowID INT IDENTITY);



In a different query window, run your command:
IF EXISTS (select * from tempdb..sysobjects where name like '%#testvm%') SELECT * FROM #testvm



As you will notice, your query does find a temp table - however you can't use it since it was created in another session.

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

WayneS
WayneS
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21534 Visits: 10652
Seth Kramer (11/22/2010)
Okay but where does the numbering at the end of the underscores correspond to the spid that created it?

Hypothetically, lets say I wanted to see what spid owned each copy of a local temp table. How would I do that? Also does it work for a global temp table.


Those last 12 characters do not correspond to the spid - they are sequentially assigned hexadecimal numbers based on the order that the temp tables are created. Run the following code (works best with results to text) to sequentially create the same temp table 100 times; you will see that the suffix appended to the table name is sequential.
SET NOCOUNT ON;
GO
if object_id('tempdb..#test') IS NOT NULL DROP TABLE #test;
create table #test (RowID INT IDENTITY PRIMARY KEY CLUSTERED);
select name from tempdb.sys.tables where name like '#test%';
GO 100



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

Hardy21
Hardy21
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2710 Visits: 1399
GO [count] is nice command to execute the batch no of times. Today's QoTD (11/23/2010) is also refer GO [count] statement. :-)

Thanks
jts2013
jts2013
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1524 Visits: 5009
What interested me was how long the random names are in sysobjects - obviously Microsoft planning for LOTS of temporary objects being created!
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