SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by VM / temp table in sysobjects / Latest Posts

RE: temp table in sysobjects

UMG Developer — Fri, 03 Dec 2010 13:20:40 GMT

Thanks for the question, and the discussion.

RE: temp table in sysobjects

archana.singh 38798 — Wed, 24 Nov 2010 21:13:19 GMT

Well said Nigel. :-)

RE: temp table in sysobjects

SQLRNNR — Wed, 24 Nov 2010 10:55:53 GMT

Thanks for the question.

RE: temp table in sysobjects

nigel. — Wed, 24 Nov 2010 01:51:13 GMT

[quote][b]archana.singh 38798 (11/23/2010)[/b][hr]This will surely work : select * from sysobjects where name like '#testvm%'[/quote]No, because it will also return tables called '#testvm_something' etc. as was explained earlier

RE: temp table in sysobjects

archana.singh 38798 — Tue, 23 Nov 2010 23:52:45 GMT

This will surely work : select * from sysobjects where name like '#testvm%'

RE: temp table in sysobjects

DLathrop — Tue, 23 Nov 2010 19:02:13 GMT

[quote][b]jts_2003 (11/23/2010)[/b][hr]What interested me was how long the random names are in sysobjects - obviously Microsoft planning for LOTS of temporary objects being created![/quote]It's not the number of names, it's to lower the chances of generating duplicate names. That's why GUID/UNIQUEIDENTIFIER values are so long.

RE: temp table in sysobjects

jts_2003 — Tue, 23 Nov 2010 09:37:05 GMT

What interested me was how long the random names are in sysobjects - obviously Microsoft planning for LOTS of temporary objects being created!

RE: temp table in sysobjects

Hardy21 — Tue, 23 Nov 2010 05:02:40 GMT

GO [count] is nice command to execute the batch no of times. Today's QoTD (11/23/2010) is also refer GO [count] statement. :-)

RE: temp table in sysobjects

WayneS — Mon, 22 Nov 2010 18:31:20 GMT

[quote][b]Seth Kramer (11/22/2010)[/b][hr]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.[/quote]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.[code="sql"]SET NOCOUNT ON;GOif 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[/code]

RE: temp table in sysobjects

WayneS — Mon, 22 Nov 2010 18:04:53 GMT

[quote][b]mbova407 (11/22/2010)[/b][hr]Correct sql is [code="sql"]select * from tempdb..sysobjects where name like '%#testvm%'[/code][/quote]Try this:in one query window, run:[code="sql"]CREATE TABLE #testvm (RowID INT IDENTITY);[/code]In a different query window, run your command:[code="sql"]IF EXISTS (select * from tempdb..sysobjects where name like '%#testvm%') SELECT * FROM #testvm[/code]As you will notice, your query does find a temp table - however you can't use it since it was created in another session.

RE: temp table in sysobjects

Seth Kramer — Mon, 22 Nov 2010 15:44:11 GMT

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.

RE: temp table in sysobjects

sknox — Mon, 22 Nov 2010 09:04:28 GMT

[quote][b]Carlo Romagnano (11/22/2010)[/b][hr][quote][b]Carlo Romagnano (11/22/2010)[/b][hr][quote][b]WayneS (11/22/2010)[/b][hr]BTW, the best way to check for the existance of a temporary table in [u]your current session[/u] is:[code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') IS NOT NULL[/code]Note that in SQL Server thru version 2008R2, temp tables are created with a positive object_id value, so you might want to use:[code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') > 0[/code][/quote]You can test <> 0I always use [code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') <> 0[/code] :-D[/quote][/quote]But as Wayne said, the [b]best[/b] 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

RE: temp table in sysobjects

Geoff A — Mon, 22 Nov 2010 08:20:10 GMT

[quote] So putting all this together we have:[code]select * from tempdb.sys.objects where object_id = OBJECT_ID('tempdb.dbo.#testvm')[/code]Hope I've not missed anything :-)[/quote]seems perfect to me....

RE: temp table in sysobjects

Carlo Romagnano — Mon, 22 Nov 2010 07:54:45 GMT

[quote][b]Carlo Romagnano (11/22/2010)[/b][hr][quote][b]WayneS (11/22/2010)[/b][hr]BTW, the best way to check for the existance of a temporary table in [u]your current session[/u] is:[code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') IS NOT NULL[/code]Note that in SQL Server thru version 2008R2, temp tables are created with a positive object_id value, so you might want to use:[code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') > 0[/code][/quote]You can test <> 0I always use [code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') <> 0[/code] :-D[/quote]

RE: temp table in sysobjects

Carlo Romagnano — Mon, 22 Nov 2010 07:53:26 GMT

[quote][b]WayneS (11/22/2010)[/b][hr]BTW, the best way to check for the existance of a temporary table in [u]your current session[/u] is:[code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') IS NOT NULL[/code]Note that in SQL Server thru version 2008R2, temp tables are created with a positive object_id value, so you might want to use:[code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') <> 0[/code][/quote]You can test <> 0I always use [code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') <> 0[/code] :-D

RE: temp table in sysobjects

nigel. — Mon, 22 Nov 2010 07:27:44 GMT

[quote][b]mbova407 (11/22/2010)[/b][hr]Correct sql is [code="sql"]select * from tempdb..sysobjects where name like '%#testvm%'[/code][/quote]Not quite.It was mentioned in an earlier post that using [b]LIKE[/b] 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:[code]select * from tempdb.sys.objects where object_id = OBJECT_ID('tempdb.dbo.#testvm')[/code]Hope I've not missed anything :-)

RE: temp table in sysobjects

mbova407 — Mon, 22 Nov 2010 06:27:59 GMT

Correct sql is [code="sql"]select * from tempdb..sysobjects where name like '%#testvm%'[/code]

RE: temp table in sysobjects

WayneS — Mon, 22 Nov 2010 06:13:50 GMT

BTW, the best way to check for the existance of a temporary table in [u]your current session[/u] is:[code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') IS NOT NULL[/code]Note that in SQL Server thru version 2008R2, temp tables are created with a positive object_id value, so you might want to use:[code="sql"]IF OBJECT_ID('tempdb..#YourTempTable') > 0[/code]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.

RE: temp table in sysobjects

RichardDouglas — Mon, 22 Nov 2010 05:27:30 GMT

Good question, thanks for taking the time to help educate the SQL community.

RE: temp table in sysobjects

Hugo Kornelis — Mon, 22 Nov 2010 04:55:37 GMT

[quote][b]Randhir Singh (11/22/2010)[/b][hr]Good question!!!I just want to add few lines here..1. sysobjects is a view not table[/quote]And a deprecated one to boot. New code should use the new system views. In this case, sys.objects.

RE: temp table in sysobjects

Randhir Singh — Mon, 22 Nov 2010 04:16:37 GMT

Good question!!!I just want to add few lines here..1. sysobjects is a view not table2. 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

RE: temp table in sysobjects

Iulian -207023 — Mon, 22 Nov 2010 03:37:39 GMT

[quote]Below query returns the required result:[code="sql"]select * from tempdb..sysobjects where name like '#testvm%' [/code][/quote]Indeed Hardy21 it works, I also tried with:[code="sql"]select * from tempdb..sysobjects where name like '#testvm' [/code]and observed it does not work, but then I saw the name column: #testvm__...__00000000000E :-)Thanks,Iulian

RE: temp table in sysobjects

Carlo Romagnano — Mon, 22 Nov 2010 03:21:18 GMT

[quote][b]vk-kirov[/b][/quote]That's true!I ran it on sql2000 sp4.

RE: temp table in sysobjects

vk-kirov — Mon, 22 Nov 2010 02:58:17 GMT

[quote][b]Carlo Romagnano (11/22/2010)[/b][hr]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%'[/quote]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:[code="plain"]name id-------------------------- -----------#a____________0000000648F9 756038366[/code]Session 2:[code="plain"]name id-------------------------- -----------#a____________0000000648FA 772038423[/code]The version of the SQL Server:[code="sql"]SELECT @@VERSION[/code][quote]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)[/quote]

RE: temp table in sysobjects

Hardy21 — Mon, 22 Nov 2010 02:29:39 GMT

Good one point question.Below query returns the required result:[code="sql"]select * from tempdb..sysobjects where name like '#testvm%'[/code]

RE: temp table in sysobjects

paul.knibbs — Mon, 22 Nov 2010 02:14:06 GMT

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! :-)

RE: temp table in sysobjects

Carlo Romagnano — Mon, 22 Nov 2010 02:05:35 GMT

[quote][b]vk-kirov (11/22/2010)[/b][hr][quote][b]Carlo Romagnano (11/22/2010)[/b][hr]Also this is wrong:[quote]If you used a query like this, you would see [b]your table[/b]: [/quote]You will see [b]ALL tables[/b] that begins with '#testvm', also, tables belonging to other sessions.[/quote]... only if you are a privileged user on the server (a member of the [b]sysadmin[/b] role, I assume). If you are an ordinary user, you can see only the temporary tables which have been created in your current connection.[/quote]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%'Here the result:name id#a__________________________________________________________________________________________________________________0000000A6831 86071260#a__________________________________________________________________________________________________________________0000000A6855 955646365

RE: temp table in sysobjects

vk-kirov — Mon, 22 Nov 2010 01:42:51 GMT

[quote][b]Carlo Romagnano (11/22/2010)[/b][hr]Also this is wrong:[quote]If you used a query like this, you would see [b]your table[/b]: [/quote]You will see [b]ALL tables[/b] that begins with '#testvm', also, tables belonging to other sessions.[/quote]... only if you are a privileged user on the server (a member of the [b]sysadmin[/b] role, I assume). If you are an ordinary user, you can see only the temporary tables which have been created in your current connection.

RE: temp table in sysobjects

Yousaf Khan — Mon, 22 Nov 2010 01:41:18 GMT

Right This query is not working if the query is running in another DB sessionand also this syntax is wrong select * from sysobjects where name = '#testvm%'if the query is like select * from sysobjects where name like '#testvm%'and the session is in tempdb then it will work otherwise it will be like bellowselect * from tempdb..sysobjects where name like '#testvm%'Yousaf Khan

RE: temp table in sysobjects

Carlo Romagnano — Mon, 22 Nov 2010 01:06:04 GMT

Also this is wrong:[quote]If you used a query like this, you would see [b]your table[/b]: [/quote]You will see [b]ALL tables[/b] that begins with '#testvm', also, tables belonging to other sessions.

RE: temp table in sysobjects

vk-kirov — Mon, 22 Nov 2010 00:48:35 GMT

[code="sql"]select * from sysobjects where name = '#testvm%'[/code]This query doesn't work and should be replaced with the following:[code="sql"]select * from tempdb.dbo.sysobjects where name like '#testvm%'[/code]

RE: temp table in sysobjects

deepak.a — Mon, 22 Nov 2010 00:17:33 GMT

Nice Question .There is a another way to Check that , using the below query create table #testvm (ID int)select * from tempdb.sys.objects where OBJECT_id= object_id('tempDB..#testvm')drop table #testvmThanks & RegardsDeepak.A

RE: temp table in sysobjects

L' Eomot Inversé — Sun, 21 Nov 2010 13:19:06 GMT

I agree with Scott, the query which the explanation says willprovide info about the table will in fact retrieve nothing unless "=" is changed to "LIKE" and it's running in the context of tempdb.

RE: temp table in sysobjects

The CosmicTrickster — Sun, 21 Nov 2010 12:56:45 GMT

Good question. Couple of issues with the construction of it though:1. Unless the database context is set to tempdb, or you explicitly reference tempdb..sysobjects, you will get no rows returned.2. If you are using the wildcard (%) character, the '=' will need to be changed to 'LIKE'. Again, no rows returned otherwise.

RE: temp table in sysobjects

Koen Verbeeck — Sat, 20 Nov 2010 12:31:28 GMT

Nice question, although a link to a reference would have been nice.(ps: quite bizare I answered the QoTD of 22th November on the 20th of November. Time travelling? :-))

temp table in sysobjects

VM-723206 — Sat, 20 Nov 2010 12:30:20 GMT

Comments posted to this topic are about the item [B]temp table in sysobjects[/B]