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

Use this stored procedure to find the disk space used by each table in a SQL Server 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 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

Dig deeper on SQL Server Stored Procedures

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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