Temp table Owner

By | January 13, 2020

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

Category: Databases

About opsadmin

This post was written by Jayanth Kurup. A Microsoft SQL Server Consultant and Trainer based out of Bangalore, India. Jayanth has been working on MS SQL Server for over 15 years. He is a performance tuning and Business Intelligence expert. Having worked with companies like Microsoft, DELL, Wells Fargo, Thomson Reuters and many other fortune 100 companies. Some other technologies Jayanth works on include Microsoft Azure, PowerBI, Python and AWS. When he isn’t consulting or training, Jayanth like to travel, paint and read. He is also very active in social causes and the founder of Enabled Business Solutions. Visit his company by clicking the link in the menu or email him directly.