I use this chunk of code at least once a week. It gives you a quick and easy reference on how big your tables are. This is about as accurate as the system stored procedure sp_spaceused (since it uses the same base tables), but it's handier, since it runs on all the tables in a database, and you can order it by whichever column you want.
Enjoy!
select TableName = convert(varchar(100),sysobjects.name) ,TotalRows = max(sysindexes.rows) ,MbData = floor(sum(convert(real,sysindexes.dpages)) * spt_values.low / 1048576) ,MbTotal = floor(sum(convert(real,sysindexes.used)) * spt_values.low / 1048576 ) from sysobjects join sysindexes on sysobjects.id = sysindexes.id join master.dbo.spt_values spt_values on spt_values.number = 1 and spt_values.type = 'E' where sysobjects.type = 'U' and indid in (0,1,255) group by sysobjects.name ,spt_values.low order by 4 desc
2016-07-04 (first published: 2016-06-08)
861 reads
This script calculates table row count and size(MB), as well as the used space and free space.
2014-01-31 (first published: 2014-01-17)
2,283 reads
Two views that give you a variety of stats, sortable as a percent of DB size, for tables, indiceses, XML indices, and Service Broker Queues.
2012-10-09 (first published: 2008-06-16)
3,055 reads
A quick script that provides the sizes of the tables in your database, including schema and choice of sort method
2018-08-07 (first published: 2018-07-29)
775 reads
