The joy of sp_spaceused
Copy and paste the code below into AdventureWorks2008 and execute against the DB you want table metrics for. The limitation of this script is that it can only give table metrics for the current DB as sp_spaceused only works for the current DB. Perhaps the next target will be to modify this so as to get the tables for all databases within a server.
/*
script to get table sizes and row count within a database using sp_spaceused.
*/-- lets create a table holder for our results.
DECLARE @t_sizes_BS TABLE (
[Name] VARCHAR(60)
,[Rows] INT
,[Reserved] VARCHAR(60)
,[Data] VARCHAR(60)
,[Index_Size] VARCHAR(60)
,[Unused] VARCHAR(60)
)
DECLARE @tab_name VARCHAR(100)
DECLARE csr_tab_sizes CURSOR FOR
-- lets get the tables together with their schema. Leaving out schema names may result with sql server throwing an error
SELECT table_Schema+ '.'+TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
OPEN csr_tab_sizes
FETCH NEXT FROM csr_tab_sizes INTO @tab_name
WHILE @@fetch_status = 0
BEGIN
INSERT INTO @t_sizes_BS
EXEC sp_spaceused @tab_name
FETCH NEXT FROM csr_tab_sizes INTO @tab_name
END
CLOSE csr_tab_sizes
DEALLOCATE csr_tab_sizes
SELECT DISTINCT [Name]
, [Rows]
, CAST(REPLACE(Reserved,'KB','') AS INT) Reserved
, CAST(REPLACE(Data,'KB','') AS INT) Data
, CAST(REPLACE(Index_Size,'KB','') AS INT) Index_Size
, CAST(REPLACE(Unused,'KB','') AS INT) Unused
, CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE()))) AS 'AsOfToday'
FROM @t_sizes_BS
ORDER BY CAST([Rows] AS INT) DESC,CAST(REPLACE(Reserved,'KB','') AS INT) DESC
-- sample results
/*
NameRowsReservedDataIndex_SizeUnusedAsOfToday
SIBS-PC24263424776247528162012-06-09 00:00:00.000
SalesOrderDetail12131715752988053125602012-06-09 00:00:00.000
TransactionHistory1134439912630431924162012-06-09 00:00:00.000
TransactionHistoryArchive892537976496025684482012-06-09 00:00:00.000
WorkOrder725916480419218324562012-06-09 00:00:00.000
*/ 
