sql query to get filesizes from a MOSS wss database
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