Identify the size and document count of a single SharePoint Site

The following query is useful, if you have a SharePoint site which uses one content database and all websites hang off the one top url.  The script can be used to identify the total size of each site and the number of documents contained in the site.  

USE [<YourContentDatabaseName>]; — don’t forget the semi-colon!

with
folderhierachy as(
select f.fullurl, (((sum(cast(d.size as bigint))) / 1024) /1024) as sizeinMB, count(d.Id) as DocCount
from webs f, docs d, lists l
where
d.webid = f.id
and
l.tp_id = d.listid
and
d.size > 0
and
(d.LeafName NOT LIKE ‘%.stp’)
AND (d.leafname NOT LIKE ‘%.aspx’)
AND (d.leafname NOT LIKE ‘%.xfp’)
AND (d.leafname NOT LIKE ‘%.dwp’)
AND (d.leafname NOT LIKE ‘%template%’)
AND (d.leafname NOT LIKE ‘%.inf’)
AND (d.leafname NOT LIKE ‘%.css’)
group by f.FullUrl
)
select substring(fullurl,1,charindex(‘/’,fullurl)) as Url, sum(sizeinMB) as “Size In MB”, sum(DocCount) as “Document Count”
from folderhierachy
group by substring(fullurl,1,charindex(‘/’,fullurl))
order by Url, [Size In MB] desc;

 

Advertisements