REM
/*
 * SQLServerTableSizes.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */
SET NOCOUNT on
CREATE TABLE #TblSize
(tname    nvarchar(80)
,rows     int
,reserved varchar(80)
,dat      varchar(80)
,ind      varchar(80)
,unused   varchar(80)
)
-- Populate the temp table size table with data for each table in the database...
EXEC sp_MSforeachtable @command1="INSERT INTO #TblSize (tname,rows,reserved,dat,ind,unused)EXEC sp_spaceused '?'"
-- Sort the data into size order (largest #rows first), showing space as MB
SELECT tname AS "Table Name"
      ,rows  AS "Row Count"
      ,CAST(reserved AS decimal(8,4)) AS reservedMB
      ,CAST(dat AS decimal(8,4)) AS dataMB
      ,CAST(ind AS decimal(8,4)) AS indexMB
      ,CAST(unused AS decimal(8,4)) AS unusedMB
      ,CAST(dat+ind+unused AS decimal(8,4)) AS sumKB
FROM (SELECT tname
            ,rows
            ,CAST(SUBSTRING(reserved,1,CHARINDEX(' ',reserved)) AS float)/1024 AS reserved
            ,CAST(SUBSTRING(dat,1,CHARINDEX(' ',dat)) AS float)/1024 AS dat
            ,CAST(SUBSTRING(ind,1,CHARINDEX(' ',ind)) AS float)/1024 AS ind
            ,CAST(SUBSTRING(unused,1,CHARINDEX(' ',unused)) AS float)/1024 AS unused
      FROM #TblSize) v 
ORDER BY rows DESC
-- Aggregate the data, showing total space as MB
SELECT CAST(SUM(reserved) AS int) AS "Total reservedMB"
      ,CAST(SUM(dat) AS int) AS "Total dataMB"
      ,CAST(SUM(ind) AS int) AS "Total indexMB"
      ,CAST(SUM(unused) AS int) AS "Total unusedMB"
      ,CAST(SUM(dat+ind+unused) AS int) AS "Total sumKB"
FROM (SELECT CAST(SUBSTRING(reserved,1,CHARINDEX(' ',reserved)) AS float)/1024 AS reserved
            ,CAST(SUBSTRING(dat,1,CHARINDEX(' ',dat)) AS float)/1024 AS dat
            ,CAST(SUBSTRING(ind,1,CHARINDEX(' ',ind)) AS float)/1024 AS ind
            ,CAST(SUBSTRING(unused,1,CHARINDEX(' ',unused)) AS float)/1024 AS unused
      FROM #TblSize) v

DROP TABLE #TblSize

SET DOC off
/* SUMMARY - Show the space used for each table in a SQL Server database and calculate the total space used.
 *
 * DETAIL - The sp_spaceused system stored procedure will give space details, including a row count for each table in the database. sp_MSforeachtable can be used as an “iterator” 
 * to run  sp_spaceused for every table in a given database. However, the results appear as a single row resultset for each and every table. The output is rather unwieldly and cannot be sorted in this format:
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
 *
 * To get a sorted list then the results of the above command must be first loaded into a temporary table. This also allows some manipulation of the data to make it more presentable. 
 * The end result is a resultset of useful information that can be sorted any which way you need and filtered as any normal SQL can. One word of caution, the @command1 parameter 
 * only allows up to 128 characters so you have to keep the command as short as possible! 
 *
 * This script uses sp_MSforeachtable to populate a temporary table and then queries this table to give a sorted list of tables and their sizes (largest first). It goes on to aggregate the space used
 * by all tables to give the values for the entire database. Some CASTing is used to prevent data loss due to rounding and to show the end results to four decimal places (an arbitrary choice).
 *
 * When using temporary tables in T-SQL there is always the temptation to replace them with the more efficient (for small data volumes at least) table variables. You cannot do this here, however, as
 * the EXEC sp_MSforeachtable @command1... construct does not support the use of table variables.
 *
 * (c) Tim Onions 2007
 * Script used at the database owners own risk.
 */
REM