The following SQL query returns the size of each table in the specified database.
USE sp_MSforeachtable @command1='EXEC sp_spaceused ''?''',@whereand='or OBJECTPROPERTY(o.id, N''IsSystemTable'') = 1'
Here’s how it works. SQL Server’s system stored procedure sp_MSforeachtable lets you execute code against every table without having to write cursors or while loops. The procedure enumerates through each table in a database and executes a command that you specify as a parameter.
I found this code very useful for estimating the size and growth of a database over time. The query works in all modern versions of SQL Server (i.e., 2000/2005/2008).