Determine size of data in tables

Sample script that can be used to determine the size of data in a set of tables in a SQL database.

/*
The following SQL script can be used to determine the size of data in 
a set of tables in a SQL 2005 database.
The script makes use of the system stored procedure, sp_spaceused.
*/
-- declare a local table variable
declare @tblObjSize as table
(
   Name varchar(255)
   , Rows int
   , Reserved varchar(50)
   , Data varchar(50)
   , index_size varchar(50)
   , unused varchar(50)
   , lReserved int default(0)
   , lData int default(0)
)

-- declare & open a cursor to iterate through each of our tables in the database.
-- Uncomment and modify the 'where' clause in the select statement to 
-- return the necessary tables.
DECLARE curTables cursor FAST_FORWARD for 
select name from sys.tables
--where name like 'ag_%'
order by name
OPEN curTables

-- declare a variable to hold the table name retrieved from the cursor
DECLARE @sObjName nvarchar(776)

-- get the 1st row of data from the cursor
FETCH next from curTables into @sObjName

-- turn off the record insert counter
SET nocount on

-- keep going while tha last fetch was good.
WHILE (@@fetch_status = 0)
begin 
-- run the system stored proc to get the space used for the current table.
INSERT into @tblObjSize ( Name, Rows, Reserved, Data, index_size, unused ) 
exec sp_spaceused @sObjName
-- get the next row of data from the cursor
FETCH next from curTables into @sObjName
end

-- turn on the record counter
SET nocount off
-- close & deallocate the cursor
CLOSE curTables
DEALLOCATE curTables

-- Update the lReserved & lData columns, removing the 'KB' and converting to int.
UPDATE @tblObjSize set
lReserved = convert(int, substring(Reserved, 0, len(Reserved) - 2))
, lData = convert(int, substring(Data, 0, len(Data) - 2))

-- output our results.
SELECT * 
from @tblObjSize
order by lData desc


declare @lReserved int, @lData int

SELECT @lReserved = sum(lReserved), @lData = sum(lData) from @tblObjSize
print 'Total Reserved: ' + convert(varchar, @lReserved) + ' KB'
print 'Total Data: ' + convert(varchar, @lData) + ' KB'

The output will look something like this: