-- Kombinierte Abfrage, die sowohl die Zeilenzahl als auch Speicherinformationen für jede Tabelle zurückgibt

WITH TablePages AS (
    SELECT s.[object_id]
         , SUM(s.reserved_page_count) AS reserved_pages
         , SUM(s.used_page_count)     AS used_pages
         , SUM(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 pages
         , (SELECT COUNT(i.[object_id]) FROM sys.indexes AS i WHERE s.[object_id] = i.[object_id] AND i.[type] <> 0) AS IndexCount
         , SUM(row_count)           AS NumberOfRows
    FROM sys.dm_db_partition_stats AS s
    GROUP BY [object_id]
)
, ExtraData AS (
    SELECT p.[object_id]
         , COUNT(*)                 AS IndexCount
         , SUM(reserved_page_count) AS reserved_pages
         , SUM(used_page_count)     AS used_pages
    FROM sys.dm_db_partition_stats  AS p
    LEFT JOIN sys.internal_tables   AS it ON p.[object_id] = it.[object_id]
    WHERE it.internal_type IN (202, 204, 211, 212, 213, 214, 215, 216)
    GROUP BY p.[object_id]
)
, CombinedData AS (
    SELECT p.[object_id]
         , OBJECT_SCHEMA_NAME(p.[object_id]) AS SchemaName
         , OBJECT_NAME(p.[object_id])        AS TableName
         , p.NumberOfRows
         , (p.reserved_pages + ISNULL(e.reserved_pages, 0)) * 8 AS ReservedKb
         , p.pages * 8 AS DataKb
         , p.IndexCount
         , CASE WHEN p.IndexCount <> 0 AND p.used_pages + ISNULL(e.used_pages, 0) >= p.pages
                THEN p.used_pages + ISNULL(e.used_pages, 0) - p.pages
                ELSE 0
           END * 8 AS IndexAllKb
         , CASE WHEN p.reserved_pages + ISNULL(e.reserved_pages, 0) > p.used_pages + ISNULL(e.used_pages, 0)
                THEN p.reserved_pages + ISNULL(e.reserved_pages, 0) - p.used_pages + ISNULL(e.used_pages, 0)
                ELSE 0
           END * 8 AS UnusedKb
    FROM TablePages     AS p
    LEFT JOIN ExtraData AS e ON p.[object_id] = e.[object_id]
    WHERE OBJECT_SCHEMA_NAME(p.[object_id]) <> 'sys' AND p.pages > 0
)
SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    SUM(p.rows) AS TotalRows,
    MAX(p.data_compression_desc) AS CompressionType,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    CAST(ROUND((SUM(a.total_pages) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    CAST(ROUND((SUM(a.used_pages) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB,
    ISNULL(cd.IndexCount, 0) AS IndexCount,
    ISNULL(CAST(ROUND(cd.IndexAllKb / 1024.00, 2) AS NUMERIC(36, 2)), 0) AS IndexAllMB
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 OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN
    CombinedData cd ON t.object_id = cd.object_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    AND p.index_id < 2
    AND OBJECT_NAME(p.object_id) NOT LIKE N'sys%'
    AND OBJECT_NAME(p.object_id) NOT LIKE N'spt_%'
    AND OBJECT_NAME(p.object_id) NOT LIKE N'queue_%'
    AND OBJECT_NAME(p.object_id) NOT LIKE N'filestream_tombstone%'
    AND OBJECT_NAME(p.object_id) NOT LIKE N'fulltext%'
    AND OBJECT_NAME(p.object_id) NOT LIKE N'ifts_comp_fragment%'
    AND OBJECT_NAME(p.object_id) NOT LIKE N'filetable_updates%'
    AND OBJECT_NAME(p.object_id) NOT LIKE N'xml_index_nodes%'
    AND OBJECT_NAME(p.object_id) NOT LIKE N'sqlagent_job%'
    AND OBJECT_NAME(p.object_id) NOT LIKE N'plan_persist%'
GROUP BY
    t.Name, s.Name, cd.IndexCount, cd.IndexAllKb
ORDER BY
    TotalSpaceMB DESC, t.Name;