sql server all temp table sizes

use tempdb

--1

select object_name(p.object_id),

    sum(au.total_pages)*8 as [space_in_kb],

    sum(au.total_pages)*8/1024 as [space_in_mb],

    sum(au.total_pages)*8/1024/1024 as [space_in_gb]

    from sys.partitions p

    join sys.allocation_units au on p.hobt_id = au.container_id

    group by p.object_id

    order by [space_in_kb]  desc

---2

SELECT sysobjects.[name] AS [TableName],

    SUM(sysindexes.reserved) * 8 AS [Size(KB)],

    SUM(sysindexes.dpages) * 8 AS [Data(KB)],

    (SUM(sysindexes.used) - SUM(sysindexes.dpages)) * 8 AS [Indexes(KB)],

    (SUM(sysindexes.reserved) - SUM(sysindexes.dpages)) * 8 AS [Unused(KB)]

FROM dbo.sysindexes AS sysindexes

    JOIN dbo.sysobjects AS sysobjects ON sysobjects.id = sysindexes.id

WHERE sysobjects.[type] = 'U'

GROUP BY sysobjects.[name]

ORDER BY [Size(KB)] DESC

recursive unique identifier with CTE

;with CTE as(

      SELECt  ID=1,

                  GID=NEWID()

            UNION ALL

      SELECT  ID+1,

                  NEWID() FROM CTE WHERE ID<100

)

 

SELECT     

      *

FROM CTE OPTION (MAXRECURSION 100)

that’s all

recursive start and end of month

 

DECLARE     @StartDate DATE = '20231215';

 

WITH cteSource(MonthSerial)

AS (

      SELECT      DATEDIFF(MONTH, '19990101', @StartDate) AS MonthSerial

 

      UNION ALL

 

      SELECT      MonthSerial - 1 AS MonthSerial

      FROM  cteSource

      WHERE MonthSerial > 0

)

SELECT      MONTH(DATEADD(MONTH, MonthSerial, '19990101')) as IMONTH,

            CAST(DATEADD(MONTH, MonthSerial, '19990101')as DATE) AS STARTOFMONTH,

            CAST(DATEADD(MONTH, MonthSerial, '19990131')as DATE) AS ENDOFMONTH

FROM  cteSource

OPTION      (MAXRECURSION 0)