MS SQL-Server > Lösungen, Rezepte, Tipps, Fragen

Wie prüft man die Größe / verwendeten Speicherplatz von Tabellen?

--USE [<DatenbankName>] -- Hier auf die gewünschte Datenbank umstellen
--GO

DECLARE @kb_1024 DECIMAL(18,4);
SET @kb_1024 = 1024;

;WITH SpacePerTable AS  -- berechnet den Speicherplatz pro Tabelle in MegaByte (MB) (JOIN über object_id)
(
SELECT      T.[object_id],
            CAST(SUM(A.total_pages * 8) / @kb_1024 AS DECIMAL(18,4)) AS TotalSpaceMB,
            CAST(SUM(A.used_pages * 8) / @kb_1024 AS DECIMAL(18,4)) UsedSpaceMB
FROM        sys.tables T

INNER JOIN  sys.indexes I
ON          T.[object_id] = I.[object_id]

INNER JOIN   sys.partitions P
ON           I.[object_id] = P.[object_id]
        AND  I.index_id = P.index_id

INNER JOIN   sys.allocation_units A
ON           P.[partition_id] = A.container_id

LEFT JOIN    sys.schemas S
ON           T.[schema_id] = S.schema_id

WHERE        is_ms_shipped = 0
GROUP BY    T.[object_id]
),
TableData AS
(
    SELECT        DISTINCT
                T.[object_id],
                S.name AS SchemaName,
                T.name AS TableName,
                P.[rows] CountRows

    FROM        sys.tables T

    INNER JOIN    sys.partitions P
    ON            T.[object_id] = P.[object_id]

    LEFT JOIN    sys.schemas S
    ON            T.[schema_id] = S.[schema_id]

    WHERE        is_ms_shipped = 0    -- Nur die eigenen Tabellen berücksichtigen (also nicht System-Tabellen)
)

SELECT        T.SchemaName,
            T.TableName,
            T.CountRows,
            SPT.TotalSpaceMB,
            SPT.UsedSpaceMB,
            CAST(
                    CAST(CASE
                            WHEN SPT.TotalSpaceMB > 0
                            THEN (SPT.TotalSpaceMB - SPT.UsedSpaceMB) * 100 / SPT.UsedSpaceMB
                        END AS DECIMAL(18,2))

                    AS VARCHAR(100)
                 ) + '%' AS FreeSpaceInPercent

FROM        TableData T

INNER JOIN    SpacePerTable SPT
ON            T.[object_id] = SPT.[object_id]