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
/*
Name Rows Reserved Data Index_Size Unused AsOfToday
SIBS-PC 242634 24776 24752 8 16 2012-06-09 00:00:00.000
SalesOrderDetail 121317 15752 9880 5312 560 2012-06-09 00:00:00.000
TransactionHistory 113443 9912 6304 3192 416 2012-06-09 00:00:00.000
TransactionHistoryArchive 89253 7976 4960 2568 448 2012-06-09 00:00:00.000
WorkOrder 72591 6480 4192 1832 456 2012-06-09 00:00:00.000
*/
