Cry How to...

List the size of each table in the database

It is sometimes useful to obtain the current storage requirements of each table in a database, i.e. the number of KB of storage currently occupied by each table. There are a number of ways to get the size of tables in a SQL Server database:

:

• Using Enterprise Manager
• Using sp_spaceused (for single table)
• Using a script

Using Enterprise Manager

SQL Server Enterprise Manager can show the space used by each table in a database:

1. Expand the databases for the server in the left hand tree view.
2. Highlight the database of interest.
3. From the toolbar menu (at the top of the form), click on "View" and check "Taskpad".
   You should now have a summary view of the database, showing its size and space available.
4. In the right hand pane there should be three tabs "General", "Table Info" and "Wizards". Click on "Table Info".
   This view will list every table together with the number of rows, size and total index size.

Using sp_spaceused (for single table)

If you only require the space used for a single table then use the stored procedure sp_spaceused. For example:

sp_spaceused 'accounts'

this returns the name, rows, reserved space, data space, index-size and unused space allocated to the table.

If you require this for a number of tables then either call sp_spaceused individually for each table or interest, or try the scripting approach below.

Using a script

The following script calls sp_spaceused for every user table in the current database. It returns these results in a single table. I have found it useful to include space allocation for the entire table as well, but if this is not required then delete the last line of the script.

set nocount on
create table #spaceused (
  name nvarchar(120),
  rows char(11),
  reserved varchar(18),
  data varchar(18),
  index_size varchar(18),
  unused varchar(18)
)

declare Tables cursor for
  select name
  from sysobjects where type='U'
  order by name asc

OPEN Tables
DECLARE @table varchar(128)

FETCH NEXT FROM Tables INTO @table

WHILE @@FETCH_STATUS = 0
BEGIN
  insert into #spaceused exec sp_spaceused @table
  FETCH NEXT FROM Tables INTO @table
END

CLOSE Tables
DEALLOCATE Tables 

select * from #spaceused
drop table #spaceused

exec sp_spaceused

These notes have been tested against SQL Server 7 and SQL Server 2000.