SQL Server - 对所有分区表大小进行分组

I'm using sp_msforeachDB to identify the size occupied at table level. In case of a partition table, is it possible to group all the partitions of a particular table and give a consolidated size occupied.

我正在使用sp_msforeachDB来识别表级别占用的大小。在分区表的情况下,是否可以对特定表的所有分区进行分组并给出合并的大小。

For instance, Table A has 12 partitions. sp_msforeachDB provides 12 rows of Table A with the size, rowcount etc. Is there any way to consolidate all the 12 rows in one single row

例如,表A有12个分区。 sp_msforeachDB提供了12行表A,其中包含size,rowcount等。有没有办法合并一行中的所有12行

SELECT  CAST(MONTH(@Date) AS VARCHAR,
        CAST(YEAR(@Date) AS VARCHAR,
        @DBName as db,
        t.Name as TableName,
    s.Name as SchemaName,
    p.Rows as RowCount,
    SUM(a.total_pages) * 8 as TotalSpaceKB
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
WHERE p.Rows > 0 and t.is_ms_shipped = 0 and i.Object_ID > 255
GROUP BY t.Name, s.Name, p.Rows

1 个解决方案

#1

Take the SUM of p.Rows instead of grouping by it.

取p.Rows的SUM而不是按它分组。

SELECT CAST(MONTH(GetDate()) AS VARCHAR),
    CAST(YEAR(GetDate()) AS VARCHAR),
    DB_NAME() as db,
    t.Name as TableName,
    s.Name as SchemaName,
    Sum(p.Rows) as [RowCount],
    SUM(a.total_pages) * 8 as TotalSpaceKB
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
WHERE p.Rows > 0 and t.is_ms_shipped = 0 and i.Object_ID > 255
GROUP BY t.Name, s.Name;