Find all empty folders in SharePoint Shared Document repositories

I typically don’t Blog about stuff I do at work but I could not find this anywhere on the Internet via the Mighty Google so I thought I would post it for others to benefit from.  My original issue was that I wanted to create a report to show any empty document repositories in our SharePoint 2010  install. After searching in vain I could only come up with some CAML queries that got close but not every shared document folder we have under a main install of SharePoint. I decided after a while of searching to augment a few SQL queries I found and go directly against the Sharepoint WSS_Content DB.

This is not supported by Microsoft so if you kill your server its not anyone’s fault but your own. Be forewarned!

SELECT DirName as [Path], LeafName as [FolderName],ItemChildCount as [Items In Folder]
Where [TYPE] = ‘1’
and DirName like ‘%Shared Documents%’
And DirName not like ‘%Shared Documents/Forms%’
and ItemChildCount = 0
and FolderChildCount = 0
and DoclibRowId is not null
Group by DirName, LeafName,ItemChildCount
order by Dirname, FolderName

I query the Docs View and only get Type of 1 which seems to be folders. I also limit the query to DirName of Shared Documents and make sure that nested folders are recursively checked. so far it seems to work for me.

I hope it helps someone out there