From BOL: Returns page and row-count information for every partition in the current database.

Return info on the amount of space used by tables (and persisted views) in your database. Executes against the current database. Summary query below is one row with total figures for each table, detailed query breaks this down to each index.

DECLARE @decSumUsedPageCount DECIMAL(18,6)

SELECT
   @decSumUsedPageCount = SUM(ps.used_page_count)
FROM
   sys.dm_db_partition_stats ps
      INNER JOIN sys.objects so ON so.object_id = ps.object_id
WHERE
   so.is_ms_shipped = CAST(0 AS BIT)

SELECT
   ss.name AS SchemaName,
   so.name AS ObjectName,
   SUM(ps.used_page_count) * 8 AS UsedSpaceKB,
   (SUM(ps.used_page_count) / @decSumUsedPageCount * 100) AS PercentageOfTotalUsedSpace
FROM
   sys.dm_db_partition_stats ps
      INNER JOIN sys.objects so ON so.object_id = ps.object_id
         INNER JOIN sys.schemas ss ON ss.schema_id = so.schema_id
      LEFT JOIN sys.indexes si ON si.object_id = ps.object_id
                              AND si.index_id = ps.index_id
WHERE
   so.is_ms_shipped = CAST(0 AS BIT)
GROUP BY
   ss.name,
   so.name

DECLARE @decSumUsedPageCount DECIMAL(18,6)

SELECT
   @decSumUsedPageCount = SUM(ps.used_page_count)
FROM
   sys.dm_db_partition_stats ps
      INNER JOIN sys.objects so ON so.object_id = ps.object_id
WHERE
   so.is_ms_shipped = CAST(0 AS BIT)

SELECT
   ss.name as SchemaName,
   so.name AS ObjectName,
   si.name AS IndexName,
   si.index_id AS index_id,
   (ps.used_page_count / @decSumUsedPageCount * 100) AS [PecentOfTotal],
   ps.in_row_used_page_count,
   ps.in_row_reserved_page_count,
   ps.lob_used_page_count,
   ps.lob_reserved_page_count,
   ps.row_overflow_used_page_count,
   ps.row_overflow_reserved_page_count,
   ps.used_page_count,
   ps.reserved_page_count,
   CASE 
      WHEN ps.index_id IN (0,1) THEN ps.row_count 
      ELSE NULL 
   END AS Row_Count
FROM
   sys.dm_db_partition_stats ps
      INNER JOIN sys.objects so ON so.object_id = ps.object_id
         INNER JOIN sys.schemas ss ON ss.schema_id = so.schema_id
      LEFT JOIN sys.indexes si ON si.object_id = ps.object_id
                              AND si.index_id = ps.index_id
WHERE
   so.is_ms_shipped = CAST(0 AS BIT)