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.
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6499 Visits: 2985
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
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: 12390 Visits: 3517
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
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: 12390 Visits: 3517
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
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

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

Group: General Forum Members
Points: 6961 Visits: 3161
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
Mr or Mrs. 500
Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)Mr or Mrs. 500 (539 reputation)

Group: General Forum Members
Points: 539 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-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: 49494 Visits: 10851
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-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: 49494 Visits: 10851
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
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: 5596 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 (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

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