You are hereBlogs / Niral's blog / Get File Sizes of Document from Sharepoint Database

Get File Sizes of Document from Sharepoint Database


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

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.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Post new comment

The content of this field is kept private and will not be shown publicly.
By submitting this form, you accept the Mollom privacy policy.