Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


temp table in sysobjects


temp table in sysobjects

Author
Message
nigel.
nigel.
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1331 Visits: 2822
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
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3583 Visits: 3232
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
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3583 Visits: 3232
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

Geoff A
Geoff A
SSChasing Mays
SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)SSChasing Mays (604 reputation)

Group: General Forum Members
Points: 604 Visits: 1790
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
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2030 Visits: 2707
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
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 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
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6222 Visits: 10398
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
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6222 Visits: 10398
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1204 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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 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