title: SQL Server - Grouping all partition table size
body: <p>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. </p>
  
  <p>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</p>
  
  <pre><code>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 &gt; 0 and t.is_ms_shipped = 0 and i.Object_ID &gt; 255
  GROUP BY t.Name, s.Name, p.Rows
  </code></pre>