1

Is there a good way to find out which user and process owns a given user created temp table in tempdb?

2

1 Answer 1

3

You can use this query, courtesy of Paul White:

SELECT SUSER_NAME(CONVERT(INTEGER, OBJECTPROPERTYEX(t.OBJECT_ID, 'OwnerId')))
FROM tempdb.sys.tables AS t
WHERE t.name LIKE N'#TempTableName%';
2
  • While it looks both promising and delightful if true, I could not get this to work. I get sa running from tempdb, regardless of how I created the #table, and NULL otherwise. Nov 10, 2020 at 20:08
  • Hmm I'm seeing that as well (though in SQL Server 2016). Did you try in 2014 or older?...I'm curious if this changed over time. Either way, I'm going to revisit and see if I can get working in later versions of SQL.
    – J.D.
    Nov 11, 2020 at 0:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.