Execute the following Microsoft SQL Server T-SQL script in SSMS Query Editor to compile the dynamic space used stored procedure:

USE AdventureWorks2008;

GO

CREATE PROC dm_spaceused

AS

  BEGIN

    WITH cteSpaceUsage([Schema],[Table],[Index],Used,Reserved,IndexRows,TableRows)

         AS (SELECT s.Name,

                    o.Name,

                    coalesce(i.Name,'HEAP'),

                    p.used_page_count * 8,

                    p.reserved_page_count * 8,

                    p.row_count,

                    CASE

                      WHEN i.index_id IN (0,1) THEN p.row_count

                      ELSE 0

                    END

             FROM   sys.dm_db_partition_stats p

                    JOIN sys.objects AS o

                      ON o.object_id = p.object_id

                    JOIN sys.schemas AS s

                      ON s.schema_id = o.schema_id

                    LEFT OUTER JOIN sys.indexes AS i

                      ON i.object_id = p.object_id

                         AND i.index_id = p.index_id

             WHERE  o.type_desc = 'USER_TABLE'

                    AND o.is_ms_shipped = 0)

    SELECT   [Schema] = coalesce(t.[Schema],'Grand Total'),

             [Table] = coalesce(t.[Table],'Total'),

             [Index] = coalesce(t.[Index],'Sub Total'),

             CASE grouping(t.[Index])

               WHEN 0 THEN sum(t.IndexRows)

               ELSE sum(t.TableRows)

             END AS [RowCount],

             sum(t.Used)     AS [Used(KB)],

             sum(t.Reserved) AS [Reserved(KB)]

    FROM     cteSpaceUsage AS t

    GROUP BY t.[Schema],

             t.[Table],

             t.[Index] WITH ROLLUP

    ORDER BY grouping(t.[Schema]),

             t.[Schema],

             grouping(t.[Table]),

             t.[Table],

             grouping(t.[Index]),

             t.[Index]

  END

GO

EXEC dm_spaceused

Related article:

Script to analyze table space usage