Temp Tables of the current connection

Question

if:

select * from tempdb.sys.tables 

I will see all the temporary tables in the system, however in this view there is no information about which connection / user each table belongs to. I am interested to find only those tables that I created in my current connection. Is there any way to do this?

thanks - e

ps yes, I could try to read every table indicated with the notion that those that succeed should be mine (in the latest versions you can’t read tables of other connections), but this is too costly an approach as there may be thousands of tables per system

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

Answer 1

Assuming you don't name your #temp tables with three consecutive underscores, this should only match your #temp tables. However, it will not take your table variables and cannot in any way change this code to select tables in a foreign connection - this only works because OBJECT_ID('tempdb..#foo') can return true only for the 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 may also be interested in the space used by each of these tables (at least for heap or clustered index), for example:

 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 can expand this to show a common space for all indices. I was not worried about joining each section, as these are #temp tables.

Answer 2

 select * from tempdb.sys.objects where object_id('tempdb.dbo.' + name, 'U') is not null AND name LIKE '#%' 

Will tell you all the tables in tempdb starting in C #, which you can access, but the Aaron script just blew me out of the water haha

Answer 3

To find out the name of the user who created the object, you just need to check the schema identifier and cross-reference with the schema 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%'