timgaunt

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
    (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
    ((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id