SharePoint Usage by List Type

SharePoint uses MS SQL Server and thus we can directly query this database to do some reporting. Below is a query that counts the number of entries for each type of list (Announcements, Contacts, Discussion Boards, Document Library, Events, Generic List, Issue List, Liks List, Image Library, InfoPath Form Library, Survey, Task List, Other). For example, if we look at the the sample results below there are 6932 documents in all the document libraries in all sites within SharePoint.

Select case tp_servertemplate when 104 then 'Announcement' when 105 then 'Contacts' when 108 then 'Discussion Boards' when 101 then 'Document Library' when 106 then 'Events' when 100 then 'Generic List' when 1100 then 'Issue List' when 103 then 'Links List' when 109 then 'Image Library' when 115 then 'InfoPath Form Library' when 102 then 'Survey' when 107 then 'Task List' else 'Other' end 'ListType', sum(tp_itemcount) as EntryCount from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate IN (104,105,108,101, 106,100,1100,103,109,115,102,107,120) and tp_itemcount > 2 -- if there are only three then it is likely the sample data or a test record group by tp_servertemplate order by 2 desc

Example Results:

Type # of Entries Document Library 6932 Generic List 400 Events 356 Issue List 328 Task List 305 Announcement 292 Links List 281 Discussion Boards 276 Survey 193 Image Library 147 Contacts 128 Other 18 InfoPath Form Library 3

While that is an interesting indicator of how much the feature is being used, it doesn’t really a good picture of how many sites are using the features. In other words, all 6932 documents could be on one site and no other site is using document libraries. Below is a query that gives the number of sites that are using each type of list.

select count(distinct(webs.fullurl)) 'NumberOfSitesThatUseType', case tp_servertemplate when 104 then 'Announcement' when 105 then 'Contacts' When 108 then 'Discussion Boards' when 101 then 'Document Library' when 106 then 'Events' when 100 then 'Generic List' when 1100 then 'Issue List' when 103 then 'Links List' when 109 then 'Image Library' when 115 then 'InfoPath Form Library' when 102 then 'Survey' when 107 then 'Task List' else 'Other' end as Type from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate IN (104,105,108,101, 106,100,1100,103,109,115,102,107,120) --and tp_itemcount > 2 and FullUrl like 'sites/%' group by tp_servertemplate order by 'NumberOfSitesThatUseType' desc

Example Results: Sites Type 135 Document Library 31 Links List 30 Announcement 27 Issue List 21 Task List 20 Discussion Boards 18 Events 13 Survey 10 Contacts 7 Image Library 7 Generic List 1 Other

List Sites To get a list of all top level sites I recommend the following query: select * from webs where FullUrl like 'sites/%' To get a list of all sites (includes sites created below sites) I recommend the following query. NOTE: This will also include sub sites like Meeting Work spaces, etc. select * from webs w join sites s on (w.siteid = s.id) where w.FullUrl like 'sites/%'

Find sites that are not using a type of list Sometimes you want to find all the sites that are NOT using a particular type of list. First thing you need to know is that each list type is stored as a number, not a human friendly string. Here are the mappings you will need to understand what the numbers mean. You will notice the above queries translate them using a case statement. Note: the tp_servertemplate field can have the following values:

• 104 = Announcement
• 105 = Contacts List
• 108 = Discussion Boards
• 101 = Document Library
• 106 = Events
• 100 = Generic List
• 1100 = Issue List
• 103 = Links List
• 109 = Image Library
• 115 = InfoPath Form Library
• 102 = Survey List
• 107 = Task List

You will need to know these number for the below query. Just change the number in the query to the type of list you want to use. select webs.fullurl as [Site Relative Url], webs.Title As [Site Title], lists.tp_title As Title, tp_description As Description, tp_itemcount As [Total Item] from lists inner join webs ON lists.tp_webid = webs.Id Where tp_servertemplate = 105 -- Contact List order by tp_itemcount desc