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 7:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 2:39 AM
Points: 1,181, Visits: 2,651
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

Post #1024322
Posted Monday, November 22, 2010 7:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 2,526, Visits: 2,401
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') &lt;&gt; 0

Post #1024344
Posted Monday, November 22, 2010 7:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 2,526, Visits: 2,401
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') &lt;&gt;  0

Post #1024346
Posted Monday, November 22, 2010 8:20 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:55 AM
Points: 514, Visits: 1,731
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....
Post #1024384
Posted Monday, November 22, 2010 9:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:18 PM
Points: 1,324, Visits: 1,693
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') &lt;&gt;  0



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
Post #1024423
Posted Monday, November 22, 2010 3:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 12:48 PM
Points: 229, 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.
Post #1024754
Posted Monday, November 22, 2010 6:04 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:51 AM
Points: 6,600, Visits: 8,900
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
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 #1024787
Posted Monday, November 22, 2010 6:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, September 13, 2014 8:51 AM
Points: 6,600, Visits: 8,900
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
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 #1024793
Posted Tuesday, November 23, 2010 5:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 7, 2014 10:22 PM
Points: 1,126, Visits: 1,387
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
Post #1025039
Posted Tuesday, November 23, 2010 9:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
What interested me was how long the random names are in sysobjects - obviously Microsoft planning for LOTS of temporary objects being created!
Post #1025266
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse