IT:AD:SQL Server:HowTo:Get Table Sizes

The following query will list all table sizes and rows… It can easily be extended to derive size per row, and from there the projected size…

SELECT 
t.NAME AS TableName,
i.name as indexName,
p.[Rows],
sum(a.total_pages) as TotalPages, 
sum(a.used_pages) as UsedPages, 
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
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
i.OBJECT_ID > 255 AND 
i.index_id <= 1
GROUP BY 
t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
object_name(i.object_id)

Alternatively, if you don't want to script things:

• In SSMS
• right click on database,
• select Reports, Standard Reports, Disk Usage by Top Tables.

  • The report will give you number of rows and kilobytes used per table.
  • But doesn't give you the number of records per row.

• http://dba.stackexchange.com/a/81246
• http://www.sqlmatters.com/Articles/Listing%20all%20tables%20in%20a%20database%20and%20their%20row%20counts%20and%20sizes.aspx