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
— Table row counts and sizes.
CREATE TABLE #FXtemp
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