Total Document Count - SharePoint 2010 - Updated SQL Query

On just about every client site I work on, the client eventually asks me for a report showing how many documents are in each library and what is the URL to that site.  I wrote several queries for SharePoint 2007 to produce these reports. However, Microsoft decided to change the database table structure for 2010.  So, I had to write a new query - execute the following query on the SQL Content Database you want the count for:


case when webs.fullurl = ''
then 'Portal Site'
else webs.fullurl
end as [Site Relative Url],
webs.Title As [Site Title],
lists.tp_title As Title,
tp_description As Description,
itemcount As [Total Item]
from lists inner join webs ON lists.tp_webid = webs.Id
inner join AllListsAux ON Lists.tp_ID = AllListsAux.ListID
Where tp_servertemplate = 101
Order By [Site Relative Url]

Categories: SharePoint 2010
Permalink | Comments (0) | Post RSSRSS comment feed