Using a stored procedure to find space used by tables

[This is a follow-up to an tip called Quick way to find space used by each table in a database.]

There is an alternative way to get the space used by all tables, using the output of the stored procedure sp_spaceused and inserting it in a temporary table. There is no need to modify any stored procedure and it has a clean output.

Just copy and paste the following code. I've been using it for this purpose for about 5 months now, with no problems.

 
-- SpaceUsedByTables
-- Guillermo Maldonado
-- 2/1/2002
-- Reports name, rows, reserved, data, index_size and space unused
-- for all user tables on a database.
declare @name sysname
set nocount on
create table #Tables
(
 [name] sysname,
 [rows] int,
 reserved varchar(20),
 data varchar(20),
 index_size varchar(20),
 unused varchar(20)
)

declare tables_cursor cursor fast_forward for
 select name from sysobjects
 where type = 'U'
open tables_cursor
fetch next from tables_cursor into @name
while @@fetch_status = 0
begin
 insert #Tables
  exec sp_spaceused @name
 fetch next from tables_cursor into @name
end
close tables_cursor
deallocate tables_cursor

select * from #tables
order by [name]

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.