Find the number of data pages in a tempdb table.

Question

Hi,

sys.allocation_units can return the amount of data pages used in a regular table.

Is there a way in which I can find out how many data pages a temp table is using?

Thanks :)

Answer 1

There is not really a difference between user and temporary tables, only diff is that SQL Server engine manage temp table
Therefore you can get temp table size in the same way, just by accessing TempDB:

SELECT TBL.name AS ObjName 
      ,STAT.row_count AS StatRowCount 
      ,STAT.used_page_count * 8 AS UsedSizeKB 
      ,STAT.reserved_page_count * 8 AS RevervedSizeKB 
FROM tempdb.sys.partitions AS PART 
     INNER JOIN tempdb.sys.dm_db_partition_stats AS STAT 
         ON PART.partition_id = STAT.partition_id 
            AND PART.partition_number = STAT.partition_number 
     INNER JOIN tempdb.sys.tables AS TBL 
         ON STAT.object_id = TBL.object_id 
ORDER BY TBL.name;