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:
SQL Server Enterprise Manager can show the space used by each table in a database:
- Expand the databases for the server in the left hand tree view.
- Highlight the database of interest.
- 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.
- 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.
If you only require the space used for a single table then use
the stored procedure
sp_spaceused. For example:
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.
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 2008, SQL Server 200 and SQL Server 7.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.