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!

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
d.webid =
l.tp_id = d.listid
d.size > 0
(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;