I have been looking for a more efficient way of getting space used requirements for all of my tables in a given database. It seems like Microsoft has answered my prayers. Microsoft has now given us the sp_MSforeachtable stored procedure. This procedure is not currently documented within BOL. The T-SQL script below demonstrates how to use this procedure to retrieve space utilization for all tables within a given database. If you have a better or different way please post and let us know!
CREATE TABLE #TemptableforspaceUsed
(name SYSNAME,
rows bigINT,
reserved VARCHAR(20),
data VARCHAR(20),
index_size VARCHAR(20),
unused VARCHAR(20))
GO
INSERT #TemptableforspaceUsed
EXEC sp_MSforeachtable ‘sp_spaceused ”?”’
update #TemptableforspaceUsed
set reserved=replace (reserved,’KB’,”)
select top 10 name, reserved
from #TemptableforspaceUsed
order by convert(bigint,reserved) desc
drop table #TemptableforspaceUsed
