SELECT
DB_NAME() AS [DatabaseName],
a3.name AS [SchemaName],
a2.name AS [TableName],
a1.rows AS [RowCount],
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS [DataKB],
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN
(a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [IndexKB],
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN
(a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN
(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> 'S' and a2.type <> 'IT'
ORDER BY a3.name, a2.name
SQL Server Administration: List the row count and space used for data and indexes for each table in your database with the following query. This is the same query that is run for the Disk Usage by Table report in Management Studio. Databases that are in 80 or below compatibility cannot use the report but you can still find out usage with the query. 
