1

if I do:

select * from tempdb.sys.tables

I will see all the temporary tables in the system, however that view does not have information about which connection/user each table belongs to. I'm interested in finding only the tables I've created on my current connection. Is there a way to do this?

thanks - e

p.s. yes, I could try reading each table listed with the notion that those that succeed should prove to be mine (on recent versions one can't read other connections' tables) but that is too costly an approach since there may be thousands of tables on the system

p.p.s. I did read Is there a way to get a list of all current temporary tables in SQL Server? which asks the right question but did not get a good answer

Community
  • 1
  • 1
ekkis
  • 9,804
  • 13
  • 55
  • 105
  • If you've created the tables yourself, on your own connection, then why not just keep track of which tables you created yourself? – Robert Harvey Jul 24 '12 at 17:47
  • @RobertHarvey - You are always so pragmatic, com'on - it's a fun question. – Hogan Jul 24 '12 at 18:12
  • @RobertHarvey - because I cannot be guaranteed that I'm the only one creating tables e.g. code I call (and to which I may not have access) may create tables I have no knowledge of – ekkis Jul 24 '12 at 18:30

3 Answers3

3

Assuming you don't name your #temp tables with three consecutive underscores, this should only pick up your #temp tables. It won't, however, pick up your table variables, nor can you change this code somehow to pick the tables on someone else's connection - this only works because OBJECT_ID('tempdb..#foo') can only return true for a table in your session.

SELECT 
  name = SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1),
  t.[object_id]
FROM tempdb.sys.tables AS t
WHERE t.name LIKE '#%[_][_][_]%'
AND t.[object_id] = 
  OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1));

You might also be interested in space used by each of these tables (at least for the heap or clustered index), e.g.:

SELECT 
    name = SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1),
    t.[object_id], 
    p.used_page_count, 
    p.row_count
FROM tempdb.sys.tables AS t
INNER JOIN tempdb.sys.dm_db_partition_stats AS p
ON t.[object_id] = p.[object_id]
WHERE t.name LIKE '#%[_][_][_]%'
AND p.index_id IN (0,1)
AND t.[object_id] = 
    OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1));

You could extend that to show total space for all indexes. I didn't bother aggregating per partition since these are #temp tables.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • @Hogan because `OBJECT_ID('tempdb..#foo')` will only return a non-null value if *you* have created a #temp table named `#foo`. If you create that table in a different window, `OBJECT_ID` can't resolve it. I am not 100% clear on how SQL Server keeps track of which #temp table belongs to which session, as that information is certainly not exposed, but it does keep track. – Aaron Bertrand Jul 24 '12 at 18:15
  • Thanks, I was just going to post if that was what you were relying on based on my tests. – Hogan Jul 24 '12 at 18:21
  • @AaronBertrand - it's a clever solution but I came to ask because I was unhappy with the requirement that names don't include 3 underscores, which one can't be guaranteed. I've also see temp tables named like `#29AFBDD0` (though I'm not sure quite how these come into existence) which would rework the logic. Honestly I can't believe there isn't a demangling function! something like `select object_name_base(name) from tempdb.sys.tables)` – ekkis Jul 24 '12 at 18:28
  • @ekkis a #temp table will have several dozen underscores in its name (actually #foo will have 112 - it is a function of 128 - 12 - the length of the name you give it). If you really want to name your #temp tables with three underscores (why?), you can change my script to look for 10, or 15. But I think that requirement is a little ridiculous. #29AFBDD0 is a table variable, and I don't know of any way to resolve that to a name or to your session. – Aaron Bertrand Jul 24 '12 at 18:36
  • 1
    @ekkis and by the way, I agree with you, there should be a way to map temp tables to a session. Please vote and comment here: http://connect.microsoft.com/SQLServer/feedback/details/285110/dmv-to-map-temp-table-session-id oh and here too: http://connect.microsoft.com/SQLServer/feedback/details/285113/allow-admins-to-drop-temp-tables-for-other-spids – Aaron Bertrand Jul 24 '12 at 18:38
  • @AaronBertrand - thanks for the links, I've upvoted and posted on both – ekkis Jul 24 '12 at 19:14
  • 1
    @AaronBertrand - I've taken your lead and made the suggestion to M$. You can upvote me at: http://goo.gl/JRS7m – ekkis Jul 24 '12 at 19:34
  • this solution suffers in situations where the tempdb catalogue is locked. I can do a 'from sys.tables with (nolock)' but, very much unfortunately, the 'object_id()' does a regular select and blocks. grr... – ekkis Apr 20 '13 at 00:25
  • Yeah several of the metadata functions are unable to respect certain isolations levels. It'd be great if there werean exception for temp tables, since they're the only ones we don't have workarounds for... – Aaron Bertrand Apr 20 '13 at 03:25
3
select * 
from tempdb.sys.objects
where object_id('tempdb.dbo.' + name, 'U') is not null 
  AND name LIKE '#%'

Would tell you all the tables in tempdb beginning with # that you can access, but Aaron's script just blew me out of the water haha

Aushin
  • 1,198
  • 1
  • 7
  • 12
0

To find out the name of the user who create the object you just need to check for the schema ID and cross reference with the Schemas table

Select sch.name as 'User Owner' from tempdb.sys.tables TBL
join tempdb.sys.schemas SCH on TBL.schema_id = SCH.schema_id
where TBL.name like '#tmp_Foo%'
Marcos Luque
  • 3
  • 3