TSQL Determine Table Size

Determine the size of database tables in a MSSQL Server Database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
SELECT
    DB_NAME() AS DatabaseNase ,
    GETDATE() AS DateCreated ,
    TableName,
    Rows ,
    Reserved ,
    Data ,
    Used - Data AS IndexSize ,
    Reserved - Used AS Unused
FROM (
        SELECT
            USR.name + '.' + OBJ.name AS TableName ,
            MIN(row_count) AS Rows ,
            SUM(8 * reserved_page_count) + MAX(COALESCE(LOBDATA.LobReserved,0)) AS reserved,
            SUM (8*
                CASE
                    WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
                    ELSE lob_used_page_count + row_overflow_used_page_count
                END
            ) AS Data,
            SUM (8*used_page_count)+MAX(COALESCE(LOBDATA.LobUsed,0)) AS Used

        FROM dbo.sysobjects AS OBJ
            INNER JOIN sys.schemas AS USR
            ON OBJ.uid = USR.schema_id

            INNER JOIN sys.dm_db_partition_stats AS PS
            ON PS.object_id = OBJ.id

            LEFT JOIN(
                SELECT
                    parent_id,
                    SUM(8*reserved_page_count) AS LOBReserved,
                    SUM(8*used_page_count) AS LOBUsed
                FROM sys.dm_db_partition_stats p
                    INNER JOIN sys.internal_tables it
                    ON p.object_id = it.object_id
        WHERE it.internal_type IN (202,204)
        GROUP BY IT.parent_id
        ) AS LOBDATA

        ON LOBDATA.parent_id = OBJ.Id
           
        WHERE OBJ.type='U'
GROUP BY USR.name + '.' + OBJ.name
) AS DT
order by Data desc
//