Tip

Quick way to find space used by each table in a database

The following is a quick method to find the disk space used by each table in a database. It uses the sp_msforeachtable stored procedure to iterate through all the tables in the database.

Enter command:

sp_msforeachtable 'sp_spaceused "?"'

The ? serves as a placeholder for each table name.

Reader Feedback

Dave W. writes: This tip works fairly well. However, it's got two problems: 1) If you have a table with '?' in its name, it will crash (I know: using ? in a table name is bad, mmmkay?); and 2) It produces somewhat unwieldy output. My solution was to modify Microsoft's sp_spaceused and create a new one called sp_spaceused_all, shown below. Here is its more compact output:

name                           rows        reserved           data               index_size         unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
Orders                         830         504 KB             160 KB             320 KB             24 KB
Order Details                  2155        328 KB             72 KB              208 KB             48 KB
Employees                      9           320 KB             232 KB             48 KB              40 KB
Categories                     8           176 KB             112 KB             32 KB              32 KB
syscolumns                     455         168 KB             64 KB              56 KB              48

    Requires Free Membership to View

KB sysindexes 79 128 KB 56 KB 16 KB 56 KB Customers 91 104 KB 24 KB 80 KB 0 KB Products 77 104 KB 8 KB 96 KB 0 KB syscomments 132 72 KB 56 KB 16 KB 0 KB Suppliers 29 72 KB 24 KB 48 KB 0 KB sysobjects 100 64 KB 16 KB 48 KB 0 KB sysreferences 13 56 KB 8 KB 48 KB 0 KB sysusers 12 56 KB 8 KB 48 KB 0 KB sysdepends 399 48 KB 16 KB 32 KB 0 KB sysfilegroups 1 40 KB 8 KB 32 KB 0 KB systypes 26 40 KB 8 KB 32 KB 0 KB Territories 53 32 KB 8 KB 24 KB 0 KB Region 4 32 KB 8 KB 24 KB 0 KB EmployeeTerritories 49 32 KB 8 KB 24 KB 0 KB Shippers 3 24 KB 8 KB 16 KB 0 KB syspermissions 53 24 KB 8 KB 16 KB 0 KB sysfiles1 2 16 KB 8 KB 8 KB 0 KB sysforeignkeys 0 0 KB 0 KB 0 KB 0 KB sysfulltextcatalogs 0 0 KB 0 KB 0 KB 0 KB sysfulltextnotify 0 0 KB 0 KB 0 KB 0 KB sysfiles 0 0 KB 0 KB 0 KB 0 KB CustomerCustomerDemo 0 0 KB 0 KB 0 KB 0 KB CustomerDemographics 0 0 KB 0 KB 0 KB 0 KB sysproperties 0 0 KB 0 KB 0 KB 0 KB sysprotects 0 0 KB 0 KB 0 KB 0 KB sysindexkeys 0 0 KB 0 KB 0 KB 0 KB sysmembers 0 0 KB 0 KB 0 KB 0 KB name rows reserved data index_size unused ------------------------------ ----------- ------------------ ------------------ ------------------ ------------------ TOTAL 4580 2440 KB 920 KB 1272 KB 248 KB
Notice the total rows & bytes used are also displayed. Additionally, you can pass a wildcarded parameter to limit the output to certain tables:
sp_spaceused_all 'Ord%'

This produces:

name                           rows        reserved           data               index_size         unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
Orders                         830         504 KB             160 KB             320 KB             24 KB
Order Details                  2155        328 KB             72 KB              208 KB             48 KB

name                           rows        reserved           data               index_size         unused
------------------------------ ----------- ------------------ ------------------ ------------------ ------------------
TOTAL                          2985        832 KB             232 KB             528 KB             72 KB

Here's the creation script. We sort our output by space used so the biggest tables are first -- but it could easily be changed to sort by # of rows or table name.

CREATE procedure sp_spaceused_all
@TablePattern varchar(128) = '%',
@updateusage varchar(5) = false          -- Param. for specifying that
                         -- usage info. should be updated.
as

--declare @TablePattern varchar(128)
--declare @updateusage varchar(5)
--set @TablePattern = '%'
--set @updateusage = 'false'       -- Param. for specifying that


declare @id    int            -- The object id of @objname.
declare @type  character(2) -- The object type.
declare   @pages    int            -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage    dec(15,0)
declare @pagesperMB      dec(15,0)


declare @objname nvarchar(776) -- The object we want size on (DWV Mod)

/*Create temp tables before any DML to ensure dynamic
**  We need to create a temp table to do the calculation.
**  reserved: sum(reserved) where indid in (0, 1, 255)
**  data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
**  indexp: sum(used) where indid in (0, 1, 255) - data
**  unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
     id        int null,
     rows      int null,
     reserved  dec(15) null,
     data      dec(15) null,
     indexp         dec(15) null,
     unused         dec(15) null
)

/*
**  Check to see if user wants usages updated.
*/

if @updateusage is not null
     begin
          select @updateusage=lower(@updateusage)

          if @updateusage not in ('true','false')
               begin
                    raiserror(15143,-1,-1,@updateusage)
                    return(1)
               end
     end


select @dbname = db_name()


declare cur cursor for
          select TABLE_NAME = convert(sysname,o.name)   /* make nullable */
          from sysobjects o
          where o.type in ('U','S') --and permissions (o.id)&4096 <> 0
            and convert(sysname,o.name) like @TablePattern
          order by 1

OPEN cur

FETCH NEXT FROM cur INTO @objname
/*
**  Check to see that the objname is local.
*/

WHILE @@FETCH_STATUS = 0
BEGIN
--print 'processing ' + @objname
     /*
     **  Try to find the object.
     */
     select @id = null
     select @id = id, @type = xtype
          from sysobjects
               where id = object_id(@objname)

     /*
     **  Does the object exist?
     */
     if @id is null
          begin
               raiserror(15009,-1,-1,@objname,@dbname)
               return (1)
          end

     /*
     **  Update usages if user specified to do so.
     */
     if @updateusage = 'true'
          dbcc updateusage(0,@objname) with no_infomsgs


     set nocount on

     /*
     **  Now calculate the summary data.
     **  reserved: sum(reserved) where indid in (0, 1, 255)
     */
     insert into #spt_space (id, reserved)
          select @id, sum(reserved)
               from sysindexes
               where indid in (0, 1, 255)
                 and id = @id

     /*
     ** data: sum(dpages) where indid < 2
     **   + sum(used) where indid = 255 (text)
     */
     select @pages = sum(dpages)
               from sysindexes
                    where indid < 2
                         and id = @id
     select @pages = @pages + isnull(sum(used), 0)
          from sysindexes
               where indid = 255
                    and id = @id
     update #spt_space
          set data = @pages
          where id = @id


     /* index: sum(used) where indid in (0, 1, 255) - data */
     update #spt_space
          set indexp = (select sum(used)
                    from sysindexes
                         where indid in (0, 1, 255)
                              and id = @id)
                   - data
          where id = @id

     /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
     update #spt_space
          set unused = reserved
                    - (select sum(used)
                         from sysindexes
                              where indid in (0, 1, 255)
                                   and id = @id)
          where id = @id

     update #spt_space
          set rows = i.rows
               from #spt_space s
               join sysindexes i on i.id = s.id
                    where i.indid < 2
                         and i.id = @id

     FETCH NEXT FROM cur INTO @objname

END

CLOSE Cur
DEALLOCATE cur

     select name = substring(object_name(id), 1, 30),
          rows = convert(char(11), rows),
          reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
                    ' ' + 'KB'),
          data = ltrim(str(data * d.low / 1024.,15,0) +
                    ' ' + 'KB'),
          index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
                    ' ' + 'KB'),
          unused = ltrim(str(unused * d.low / 1024.,15,0) +
                    ' ' + 'KB')
     from #spt_space s, master.dbo.spt_values d
     where d.number = 1
       and d.type = 'E'
     order by s.reserved * d.low desc

     select name = cast('TOTAL' as char(30)),
          rows = convert(char(11), SUM(rows)),
          reserved = ltrim(str(SUM(reserved * d.low) / 1024.,15,0) +
                    ' ' + 'KB'),
          data = ltrim(str(SUM(data * d.low) / 1024.,15,0) +
                    ' ' + 'KB'),
          index_size = ltrim(str(SUM(indexp * d.low) / 1024.,15,0) +
                    ' ' + 'KB'),
          unused = ltrim(str(SUM(unused * d.low) / 1024.,15,0) +
                    ' ' + 'KB')
     from #spt_space s, master.dbo.spt_values d
     where d.number = 1
       and d.type = 'E'


return (0)

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 May 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.