How to get sizes for tables in a SQL 2008 database

Under the heading of “often needed and never remembered”, I decided to post a quick T-SQl example of how you can retrieve the space used by the various tables in a SQL 2008 database. 

Note, in my select query I am limiting to just the top 20 largest tables.  I am also ordering by the space the data is using in the table.  These can be easily modified to suit the needs.

SET NOCOUNT ON
DBCC UPDATEUSAGE(0)
— Table row counts and sizes.
CREATE TABLE #FXtemp
(
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)
INSERT #FXtemp EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

SELECT top 20 *
FROM   #FXtemp order by cast(replace(data ,’KB’,”) as int) desc
 
DROP TABLE #FXtemp