john pfeiffer
  • Home
  • Categories
  • Tags
  • Archives

Sql query to get filesizes from a MOSS wss database

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

  • « Visual c sharp crm sdk discovery service example
  • file extractor »

Published

May 28, 2009

Category

sql

~100 words

Tags

  • database 4
  • filesizes 1
  • moss 1
  • sql 18
  • sql query 1
  • wss 10