Wednesday, June 13, 2012

Overview of using fine-grained permissions

Query to fetch list item counts from Content DB

Returns the count of items in all lists in a web application:



select [***].[dbo].[AllWebs].Title,[***l].[dbo].[AllWebs].FullUrl, tp_Title,tp_ServerTemplate,UserDataCount,DocsCount
from [***].[dbo].[AllLists] left outer join
(select tp_ListId, COUNT(*) as UserDataCount from [***].[dbo].[AllUserData]
group by tp_ListId ) E On E.tp_ListId = AllLists.tp_ID
left outer join
(select ListId, COUNT(*) as DocsCount from [***].[dbo].[AllDocs]
group by ListId ) P ON AllLists.tp_ID = P.ListId
left outer join [***].[dbo].[AllWebs] ON AllLists.tp_WebId = [***].[dbo].[AllWebs].Id
order by DocsCount desc