How do I view the connections keeping a global temporary table in existence?
-
A global temporary table exist until the last connection that was using it is closed down. How can I view which connections are using a global temporary table?
SQL Server must keep track of this information, so how do I see it?
I will try to write this next bit in a kind way: I don't want a conversation about not using global temporary tables, how and why the code should be rewritten, how I should be handling the various stages of product development to ensure this never happens, and so on. I would really like to know if there is a simple answer to the question above.
Hopefully that last bit came across ok, I would like to focus on the question and not the reasons around it. Please note, if there are two ways that something can be taken, then I meant the nice way.
-
Check if you get some help from sys.tables in tempDB database.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
Thanks for the tips but unfortunately they don't go quite far enough.
The sys.tables view simply shows which temporary tables exist in the TempDB and when they were last accessed but not who accessed them.
The sys.dm_exec_sql_text only shows the most recent SQL statement issued on the session ID and by running another statement it is very easy to hide the fact that the temporary table was accessed previously through this session ID.
Knowing that SQL Server is keeping track of which connections access each table it should be simple to do but I am finding it very hard to detect how it is done....
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply