Recently I came across a post on LinkedIn which required identifying the user who created a temp table. This is an interesting use case and thought I’d write a query of my own. The below script identifies currently logged in users who are executing queries and anytime tables that they might have created.
I figured since Temp tables are only relevant within the section context it didn’t make sense to historically track the presence. I’ve used the transaction log file of the temp database to identify currently created temp tables and map them to session ids that are active based on the transaction ID.
SELECT DISTINCT t.spid
,a.AllocUnitName
,es.login_name
FROM sys.fn_dblog(2, NULL) t
CROSS APPLY (
SELECT DISTINCT [Transaction ID]
,AllocUnitName
FROM sys.fn_dblog(2, NULL)
WHERE AllocUnitName LIKE '%#%'
AND t.[transaction id] = [transaction id]
) a
INNER JOIN sys.dm_exec_sessions es ON es.session_id = t.SPID
WHERE t.SPID IS NOT NULL
Please Consider Subscribing
