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:
If you only require the space used for a single table then use
the stored procedure
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
The following script calls
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.