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?

  • Error at ps.executeQuery(sql) while using sql with timestamps
  • why is there the error `Attempting to set a non-NULL-able column's value to NULL` on merge command sometimes?
  • How to add SQL user-defined function to entity framework?
  • SQL Help: Select statement Concatenate a One to Many relationship
  • How to sort multiple columns in SQL Server according to the date
  • Database schema for hierarchical groups
  • 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.

      name = SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1),
    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.:

        name = SUBSTRING(t.name, 1, CHARINDEX('___', t.name)-1),
    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%'
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.