Get TempTable name

Question

Post reply

Get TempTable name

mobasha

SSCertifiable

Points: 5334
More actions
June 18, 2008 at 11:59 pm

#99155

hello.
is there a way to get the Global TempTable names created in the session.
..>>..
MobashA

Viewing 15 posts - 1 through 14 (of 14 total)

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

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]Such temporary objects gets stored into TempDB database. you will get it from there.

Select * From sysobjects where Name = '{temp table name}'

Mahesh[/font]

MH-09-AM-8694

mobasha SSCertifiable Points: 5334 More actions · Answer 2

????

if u know the temptableName '{temp table name}'

then i dont need to search for the temptableName '{temp table name}'

..>>..

MobashA

dolfa SSC Journeyman Points: 82 More actions · Answer 3

Hi

Try use this query:

select name from tempdb..sysobjects where name LIKE '#%'

This should select all temptables in the session.

mobasha SSCertifiable Points: 5334 More actions · Answer 4

this wont help cos i need to get the user name or the session id for the user how created this temp table.

..>>..

MobashA

John Rowan SSC Guru Points: 56440 More actions · Answer 5

From a best practices standpoint, I'd shy away from using global temp tables. They cause more headaches than help. What exactly are you trying to do? Can you give us a little more background on the purpose of the table, how it is getting created, what you are planning on doing with it?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mobasha SSCertifiable Points: 5334 More actions · Answer 6

i have a system our company use, and i need to do some modifications in the backround.

the system create a temptable named dynamiclly.

so i need to get the temptable created by the user session....

..>>..

MobashA

John Rowan SSC Guru Points: 56440 More actions · Answer 7

Well, here's the catch. You can't create a global temporary table without explicitly using the CREATE TABLE ## statement. So how, when they create the table, do they come up with the name? They've got to be using some sort of pattern or algorithm to come up with the name. If you can't somehow narrow down the name to ensure that you grabbing the correct global temp table, you're in trouble.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

mobasha SSCertifiable Points: 5334 More actions · Answer 8

i really wish if i could do so, but i have spent soo much time on it, i just solved 10% from it..

u know, i dont post something here unless i found no solution.

thanks...

..>>..

MobashA

John Rowan SSC Guru Points: 56440 More actions · Answer 9

I understand that you've only posted this becuase you've ran into a roadblock. All I'm saying is that in order to find the table, you have to be able to identify which table to grab. If you had 10 sessions open to the database that all had global temp tables open, how to you know that you are getting the right one?

The processes that create these tables have to intellegently name them so that another process does not attempt to create a global temp table with the same name. So there must be a method or pattern that they are using to create the name and in order for you to make sure that you are getting the right global temp table, you too must have some way to identify the table.

Have you tried to run Profiler against the application and pick up the CREATE TABLE ## statement? If you were to run the app and wait until it reached the point to where it uses the table and run a SELECT * FROM tempdb..sysobjects WHERE xtype = 'U' what would that return?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Ron McCullough SSC Guru Points: 63877 More actions · Answer 10

Try this to determine if it gives you enough information, using any database on the server (preferably a development db NOT a production db

CREATE TABLE ##MyTempTable (cola INT PRIMARY KEY)

INSERT INTO ##MyTempTable VALUES (1)

WAITFOR DELAY '0:05'; -- delay for 5 minutes for testing

In ANOTHER query window in SSMS execute the following T-SQL:

USE Tempdb

GO

SELECT * FROM sys.sysobjects WHERE Type = 'U'

Check BOL for the definition of the columns returned.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 11

Using BOL partial definition of column in sys.sysobjects

Name Type Descripition

uid smallint Schema ID of the owner of the object.

For databases upgraded from an earlier

version of SQL Server, the schema ID is

equal to the user ID of the owner.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 12

Mobasha,

I'm pretty sure what you ask cannot be done. You would have to capture the session name at the time of creation of the global temp table or something similar. Sorry...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mobasha SSCertifiable Points: 5334 More actions · Answer 13

althoug u have said that i cant, but it is a good sanwer i will use.

thanks

..>>..

MobashA

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 14

Mobasha,

To be true, there is a UID (User ID) column in the TempDB..SysObjects comapatibility view of SQL Server 2005... it's not the ID of the person that made it, though... it's the ID of the table owner. If you have an application where everyone is considered to be "SA" or "DBO" it won't do you much good. You can see all of the ## and # objects there... I just don't see anything to identify a SPID or anything else that relates to a session that created the table.

I know the system knows because it has to so it knows when to drop it... I'm just pretty sure there's no way for us to know programatically.

I'll keep looking...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)