Associating Temp Table with a SPID

[bikerboy718]

I have a server which has multiple databases. Each database has identical stored procedures that create temp tables in tempdb. Each temp table has a unique table name stored in the tempdb..sysobjects table for example:

#testtable______________________1234567
#testtable______________________2345678

How can I associate a temp table with the corresponding spid in which it was created?

With Great Power Comes Great Responsibility!!!

Michael

 

[gmmastros]

Why do you want to do this? Normally you would just leave these things alone.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bikerboy718]

Oh trust me I know. Long story short. We host a server that has a number of client databases. The application that connects to the database keeps a dedicated connection to the server open. The stored procedures that run on the server are tempdb intensive. If the users keep the sessions open (which is a very common problem) it never dumps the tempdb. I would like to put in drop table commands for the temp tables but I want to ensure that they exist first.

With Great Power Comes Great Responsibility!!!

Michael

 

[bikerboy718]

I actually figured out how do to relate the session to the temp table instance in the tempdb. If you look up the table by object id from your session it will give you the id of the object from your session.

DECLARE @OBJID BIGINT
SELECT @OBJID = OBJECT_ID('TEMPDB..TABLENAME')
SELECT NAME FROM TEMPDB..SYSOBJECTS WHERE ID = @OBJID

With Great Power Comes Great Responsibility!!!

Michael