[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 firstname.lastname@example.org 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.