I have written following Microsoft SQL Server T-SQL scirpt to quickly determine space used for each table in a SQL Server database. This script returns following information for each table in the database:

• SchemaName – Name of the schema.
• TableName – Name of the table.
• TableType – Type of the table e.g. Heap or Cluster.
• FileGroupName – FileGroup where the table is stored.
• NumberOfPartitions – Number of partitions in the table.
• NumberOfRows – Number of rows in the table.
• TotalDataPages – Number of data pages in the table.
• SizeOfDataPagesKB – Size of data pages in KB.
• NumberOfIndexes  - Number of indexes in the table.
• NumberOfIndexPages  –  Number of index pages for the table indexes.
• SizeOfIndexPagesKB – Size of index pages in KB.

Script:

USE [<Database Name>]
GO
WITH DataPages AS
(
SELECT o.object_id
, COALESCE(f.name,d.name) AS Storage
, s.name AS SchemaName
, o.name AS TableName
, COUNT(DISTINCT p.partition_id) AS NumberOfPartitions
, CASE MAX(i.index_id) WHEN 1 THEN 'Cluster' ELSE 'Heap' END AS TableType
, SUM(p.rows) AS [RowCount]
, SUM(a.total_pages) AS DataPages
FROM sys.tables o
JOIN sys.indexes i 
    ON i.object_id = o.object_id
JOIN sys.partitions p 
    ON p.object_id = o.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a 
    ON a.container_id = p.partition_id
JOIN sys.schemas s 
    ON s.schema_id = o.schema_id
LEFT JOIN sys.filegroups f 
    ON f.data_space_id = i.data_space_id
LEFT JOIN sys.destination_data_spaces dds
    ON dds.partition_scheme_id = i.data_space_id
    AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups d ON d.data_space_id = dds.data_space_id
WHERE o.type = 'U'
AND i.index_id IN (0,1)
GROUP BY s.name
, COALESCE(f.name,d.name)
, o.name
, o.object_id )
,IndexPages AS
(SELECT o.object_id
, o.name AS TableName
, COALESCE(f.name,d.name) AS Storage
, COUNT(DISTINCT i.index_id) AS NumberOfIndexes
, SUM(a.total_pages) AS IndexPages
FROM sys.objects o
JOIN sys.indexes i 
    ON i.object_id = o.object_id
JOIN sys.partitions p 
    ON p.object_id = o.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a 
    ON a.container_id = p.partition_id
LEFT JOIN sys.filegroups f 
    ON f.data_space_id = i.data_space_id
LEFT JOIN sys.destination_data_spaces dds
    ON dds.partition_scheme_id = i.data_space_id
    AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups d 
    ON d.data_space_id = dds.data_space_id
WHERE i.index_id <> 0
GROUP BY o.name
, o.object_id
, COALESCE(f.name,d.name))
 
SELECT t.[SchemaName]
, t.[TableName]
, t.[TableType]
, t.[Storage] AS FileGroupName
, t.[NumberOfPartitions]
, t.[RowCount]
, t.[DataPages]
, (t.[DataPages] * 8) AS SizeOfDataPagesKB
, ISNULL(i.[NumberOfIndexes],0) AS NumberOfIndexes
, ISNULL(i.[IndexPages],0) AS IndexPages
, (ISNULL(i.[IndexPages],0) * 8) AS SizeOfIndexPagesKB
FROM DataPages t
LEFT JOIN IndexPages i
    ON i.object_id = t.object_id
    AND i.Storage = t.Storage;
GO

 
Sample output after running this against the AdventureWorks database:

I hope you find it useful!