Temp tables on the current connection
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
3 Solutions collect form web for “Temp tables on the current connection”
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.
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
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%'