The following query gives a sizes of tables and number of records for the database:
select o.name , reservedpages = sum(a.total_pages) , usedpages = sum(a.used_pages) , pages = sum(case when a.type <> 1 then a.used_pages when p.index_id < 2 then a.data_pages else 0 end) , SUM(a.used_pages)*8096 AS 'Size(B)' , rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end) from sys.objects o join sys.partitions p on p.object_id = o.object_id join sys.allocation_units a on p.partition_id = a.container_id where o.type = 'U' group by o.name order by 3 desc
or similar:
SELECT t.NAME AS TableName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, p.Rows ORDER BY 3 DESC