How to get table sizes for the database

sp_spaceused (Transact-SQL)

You can use this stored procedure (T-SQL) to get a report of the table sizes for the database.

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or SQL Server 2005 Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

Sample Code

1. One table
exec sp_spaceused [tableName]

2. All tables in your database

DECLARE @@TableName Nvarchar(100)

DECLARE my_cursor CURSOR FOR
Select sysobjects.name From sysobjects Where xtype='u' OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @@TableName
WHILE @@FETCH_STATUS = 0
BEGIN

exec sp_spaceused @@TableName

FETCH NEXT FROM my_cursor INTO @@TableName
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO


Result value

Column name	Data type	Description
name	nvarchar(128)	Name of the object for which space usage information was requested. The schema name of the object is not returned. If the schema name is required, use the sys.dm_db_partition_stats or sys.dm_db_index_physical_stats dynamic management views to obtain equivalent size information.
rows	char(11)	Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.
reserved	varchar(18)	Total amount of reserved space for objname.
data	varchar(18)	Total amount of space used by data in objname.
index_size	varchar(18)	Total amount of space used by indexes in objname.
unused	varchar(18)	Total amount of space reserved for objname but no yet used.