If you are looking to get the size information for all tables in the database you can use this query:
SELECT O.type_desc,
IndexSize.obj_name,
I.name AS index_name,
IndexSize.reserved_MB,
IndexSize.used_MB,
IndexSize.row_count,
IndexSize.object_id,
IndexSize.index_id
FROM (
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(DDPS.object_id))+'.'+QUOTENAME(OBJECT_NAME(DDPS.object_id)) obj_name,
SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
SUM(DDPS.used_page_count)/128.0 AS used_MB,
SUM(row_count) AS row_count,
DDPS.object_id,
DDPS.index_id
FROM sys.dm_db_partition_stats AS DDPS
GROUP BY DDPS.object_id,DDPS.index_id
)IndexSize
JOIN sys.objects AS O
ON IndexSize.object_id = O.object_id
JOIN sys.indexes AS I
ON IndexSize.object_id = I.object_id
AND IndexSize.index_id = I.index_id;
It returns the reserved megabytes and the used megabytes as well as the row count for every index in the database. The first column tells you the object type. This includes system_tables as well as indexed views. If you want a subset filter on that column.
If you do not need the per index detail you can use this query instead:
SELECT O.type_desc,
ObjectSize.obj_name,
ObjectSize.reserved_MB,
ObjectSize.used_MB,
ObjectSize.row_count
FROM(
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(IndexSize.object_id))+'.'+QUOTENAME(OBJECT_NAME(IndexSize.object_id)) obj_name,
SUM(IndexSize.reserved_MB) AS reserved_MB,
SUM(IndexSize.used_MB) AS used_MB,
MAX(IndexSize.row_count) AS row_count,
IndexSize.object_id
FROM(
SELECT SUM(DDPS.reserved_page_count)/128.0 AS reserved_MB,
SUM(DDPS.used_page_count)/128.0 AS used_MB,
SUM(row_count) AS row_count,
DDPS.object_id
FROM sys.dm_db_partition_stats AS DDPS
GROUP BY DDPS.object_id,DDPS.index_id
)IndexSize
GROUP BY IndexSize.object_id
)ObjectSize
JOIN sys.objects AS O
ON ObjectSize.object_id = O.object_id
It gives the same information, but on an object basis. Again, filter on the object type if you are for example not interested in system_tables.
If you are just interested in normal tables, you can get the information in SSMS too by using the "Object Explorer Details" tab:
You can open it by selecting the table folder in the database in Object Explorer and then pressing F7.
You might have to add the size columns. For that just right-click on the column list and select the columns you want to see:
