Partition Table Filegroup Data Records / Number of Rows

SELECT

      SCHEMA_NAME(t.schema_id) AS SchemaName

     ,OBJECT_NAME(i.object_id) AS ObjectName

     ,p.partition_number AS PartitionNumber

     ,fg.name AS Filegroup_Name

     ,rows AS 'Rows'

     ,au.total_pages AS 'TotalDataPages'

     ,CASE boundary_value_on_right

        WHEN 1 THEN 'less than'

        ELSE 'less than or equal to'

     END AS 'Comparison'

    ,value AS 'ComparisonValue'

    ,p.data_compression_desc AS 'DataCompression'

    ,p.partition_id

FROM sys.partitions p

    JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id

    JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id

    JOIN sys.partition_functions f ON f.function_id = ps.function_id

    LEFT JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id

    JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number

    JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id

    JOIN (SELECT container_id, sum(total_pages) as total_pages

            FROM sys.allocation_units

            GROUP BY container_id) AS au ON au.container_id = p.partition_id 

    JOIN sys.tables t ON p.object_id = t.object_id

WHERE i.index_id < 2

ORDER BY ObjectName,p.partition_number;

Table Size and Rows

declare @t table (

name nvarchar(100), [rows] int, [reserved] nvarchar(100), [data] nvarchar(100), [index_size] nvarchar(100), [unused] nvarchar(100)

)

declare @name nvarchar(100)

declare tt cursor for

Select  QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [Name] from 

sys.objects AS sOBJ

 INNER JOIN sys.partitions AS sPTN

 ON sOBJ.object_id = sPTN.object_id WHERE

 sOBJ.type = 'U'AND sOBJ.is_ms_shipped = 0x0

 AND index_id < 2 -- 0:Heap, 1:Clustered

 GROUP BY sOBJ.schema_id, sOBJ.name

---Select name from sys.tables sOBJ

open tt

fetch next from tt into @name

while @@FETCH_STATUS = 0

begin

  insert into @t

  exec sp_spaceused @name

  fetch next from tt into @name

end

close tt

deallocate tt

select name as table_name, [rows] as rows_count, data + [index] as total_size, data as data_size, [index] as index_size

from (select name,

[rows],

cast (LEFT(data, LEN(data)-3) as int) data,

cast (LEFT(index_size, LEN(index_size)-3) as int) [index]

 from @t

) x

order by 3 desc, 1


OR

SELECT SCH.name AS SchemaName

      ,OBJ.name AS ObjName

      ,OBJ.type_desc AS ObjType

      ,INDX.name AS IndexName

      ,INDX.type_desc AS IndexType

      ,PART.partition_number AS PartitionNumber

      ,PART.rows AS PartitionRows

      ,STAT.row_count AS StatRowCount

      ,STAT.used_page_count * 8 AS UsedSizeKB

      ,STAT.reserved_page_count * 8 AS RevervedSizeKB

FROM sys.partitions AS PART

     INNER JOIN sys.dm_db_partition_stats AS STAT

         ON PART.partition_id = STAT.partition_id

            AND PART.partition_number = STAT.partition_number

     INNER JOIN sys.objects AS OBJ

         ON STAT.object_id = OBJ.object_id

     INNER JOIN sys.schemas AS SCH

         ON OBJ.schema_id = SCH.schema_id

     INNER JOIN sys.indexes AS INDX

         ON STAT.object_id = INDX.object_id

            AND STAT.index_id = INDX.index_id

ORDER BY SCH.name

        ,OBJ.name

        ,INDX.name

        ,PART.partition_number