Size of each and every document on SharePoint

Written by Administrator

Tuesday, 13 April 2010 13:25

I had to find out the sizes of all docuemnts uploaded to SharePoint and their location (works with MOSS and WSS)
Thanks to coleague of mine Martin B, here is the script that will query content DB you run in on and will show you nice table with Document name, Library name, Site Url and of course its size.
You can specify which files should be included and excluded in report and also from which size it should be reported (just modify last 0 in script)

SELECT Webs.FullUrl As SiteUrl, Webs.Title 'Library', DirName + '/' + LeafName
AS 'Document',
cast(round(cast(isnull(Size,0) as numeric(20,10))/1024/1024,2) as numeric(10,2)) as 'Size in MB'
FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = SItes.Id
WHERE
Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')
AND (LeafName NOT LIKE '%.aspx')
AND (LeafName NOT LIKE '%.xfp')
AND (LeafName NOT LIKE '%.dwp')
AND (LeafName NOT LIKE '%template%')
AND (LeafName NOT LIKE '%.inf')
AND (LeafName NOT LIKE '%.css')
AND cast(round(cast(isnull(Size,0) as numeric(20,10))/1024/1024,2) as numeric(10,2)) > 0
ORDER BY 'Size in MB' DESC