SQL Script to find Temporary Tables and Their Size

The Following script will help us to understand, how many temporary tables resides in tempdb and what’s the size for those tables.
There might be a case in where developers has access, where developer keep creating tables in tempdb for backup or temporary storage, but they do forget to drop those which might result tempdb oversized, In that case, we want to delete some temporary tables create in tempdb , which consomes the most storage.

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