August 13, 2018 at 1:48 am
Hi, I need to find out the session id/connection id that created currently existing global temp/temporary tables
Thank you for in advance. Br, Adrian
August 13, 2018 at 7:42 am
I'm not sure that's possible unless you have some kind of DDL trigger or monitoring in place. So if you weren't already getting such information, you may or may not be able to get it from any of the DMV's.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 13, 2018 at 12:27 pm
I believe session_ids can be re-used, so getting a session_id wouldn't necessarily tell you anything.
As noted, if you need to get that info, add a trigger to capture it at the time the table is created.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 18, 2018 at 3:48 am
If you're running a default trace and global #t-table has a PK, you might get the info from the default trace data if it hasn't rolled over yet
CREATE TABLE ##t (i INT PRIMARY KEY)
DECLARE @path NVARCHAR(255)
SELECT @path = path
FROM sys.traces
WHERE id = 1 -- default trace
SELECT LoginName, LoginSid, ObjectName, *
FROM ::fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 46 -- Create
AND ObjectName LIKE '%##t%'
ORDER BY starttime DESC
August 18, 2018 at 4:19 am
A better question might be what you are trying to accomplish by doing this? I mean you could just add columns to the temp table with the SPID and current time and get it that way. Knowing what you want to accomplish by doing this might help point you in the direction of alternative solutions.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply