Getting space used from tables - in one command
This isn't a new tip - you may have seen many references to usign sp_MSForEachTable and it's partner sp_MSForEachDB - just put it into context with getting the data back out in a clean format by using insert .. exec
/* create a table to hold the results of the exec command */
create table #SpaceUsed (name nvarchar(129), rows char(11), reserved varchar(18),data varchar(18), index_Size varchar(18), unused varchar(18))
insert #SpaceUsed exec sp_msforeachTable 'sp_spaceused "?"'
--Run the script - then
select * from #SpaceUsed order by cast(rows as int) desc
--gives you the number of rows
select * from #SpaceUsed order by cast(left(reserved,len(reserved) - 3)as int) desc
--Gives you the data based on space used (including indexes)