temp table in sysobjects

Question

Post reply

temp table in sysobjects

Viewing 15 posts - 16 through 30 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Randhir Singh SSC Enthusiast Points: 164 More actions · Answer 1

Good question!!!

I just want to add few lines here..

1. sysobjects is a view not table

2. 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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

Randhir Singh (11/22/2010)
Good question!!!
I just want to add few lines here..
1. sysobjects is a view not table

And a deprecated one to boot. New code should use the new system views. In this case, sys.objects.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

RichardDouglas SSCarpal Tunnel Points: 4029 More actions · Answer 3

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

Hope this helps,
Rich

[p]
[/p]

WayneS SSC Guru Points: 95460 More actions · Answer 4

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

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.

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 5

Correct sql is

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

nigel. SSChampion Points: 11636 More actions · Answer 6

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
How to post questions

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 7

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 😀

Carlo Romagnano SSC-Insane Points: 22670 More actions · Answer 8

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 😀

Geoff A SSChampion Points: 11511 More actions · Answer 9

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 SSChampion Points: 12388 More actions · Answer 10

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 😀

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 Ten Centuries Points: 1013 More actions · Answer 11

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 SSC Guru Points: 95460 More actions · Answer 12

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 13

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
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 14

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 Hall of Fame Points: 3226 More actions · Answer 15

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