This is a simple query to get file sizes from a sharepoint database.  I know there are multiple ways of doing this, but I found this the easiest and also the fastest.

SELECT [filename],
 SUM(CAST(( CAST(CAST(filesize AS DECIMAL(38, 2)) / 1024 AS DECIMAL(38, 2)) /  1024 )
                    AS DECIMAL(38, 2))) AS 'Size in MB'
FROM   (SELECT dirname + '/' + leafname AS [filename],
               size                     AS filesize,
               siteid,
               webid,
               extensionforfile
        FROM   alldocs
        UNION
        SELECT d.dirname + '/' + d.leafname AS [filename],
               v.size                       AS filesize,
               d.siteid,
               d.webid,
               extensionforfile
        FROM   alldocs d
               INNER JOIN alldocversions v
                 ON d.siteid = v.siteid
                    AND d.id = v.id) AS results
       INNER JOIN webs s
         ON s.siteid = results.siteid
            AND s.id = results.webid
WHERE  ( filesize IS NOT NULL
         AND filesize > 0 )
       AND extensionforfile NOT LIKE '%aspx%' -- Not Include Certian File Types
GROUP  BY [Filename]
ORDER  BY 2 DESC 
by Niral on Fri, 12/09/2008 - 11:30am   •  

Comments

Hey, this is a very neat idea.  Though, I am a bit scared of touching MOSS database, this is the easiest and most efficient way to do this.