Using a stored procedure to find space used by tables

Get the space used by all tables by using the stored procedure sp_spaceused and inserting the output in a temporary table.

This Content Component encountered an error

[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.

This was first published in February 2005

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close