SharePoint Web Template types

SharePoint has different types of templates that you can choose when you create a web site. The different types are

• Blog
• Central Admin
• Invalid (not really a type)
• Meeting
• Team Collaboration Site
• Wiki

I wanted to find out how users were using SharePoint. I know SharePoint has a MS SQL Server backend where it stores all its data. The data is stored in the Content database. In Windows SharePoint Services 2.0 (WSS2) and SharePoint Portal Server 2003 (SPS) the content database typically ends in _SITE. In Windows SharePoint Services 3.0 (WSS3) and Microsoft SharePoint Server 2007 (MOSS) the database is typically called something like _Content. For example WSS_Content.

There is no web template table that enumerates all the types of web templates. However, web sites which are stored in the Webs table in the content database do store what kind of web template was used to create the site. The WebTemplate column of the Webs table is where this value is stored

Now you can do a SQL CASE statement in your select statement to give meaningful type descriptions instead of number. For example:

select
case WebTemplate
WHEN 9 THEN 'Blog'
WHEN 3 THEN 'Central Admin'
WHEN -1 THEN 'Invalid'
WHEN 2 THEN 'Meeting'
WHEN 1 THEN 'Team Collaboration Site'
WHEN 4 THEN 'Wiki'
END
from webs

That is not very useful query though. I recommend the following query that enumerates each of the web template types and reports how many sites are of each type.

select
case WebTemplateCode
WHEN 9 THEN 'Blog'
WHEN 3 THEN 'Central Admin'
WHEN -1 THEN 'Invalid'
WHEN 2 THEN 'Meeting'
WHEN 1 THEN 'Team Collaboration Site'
WHEN 4 THEN 'Wiki'
END as WebTemplate2,
count(w.webtemplate) as NumOfSites
from Webs w
right outer join (select 9 as WebTemplateCode union select 3 union select -1 union select 2 union select 1 union select 4) webTemplates
on (w.WebTemplate = webTemplates.WebTemplateCode)
group by WebTemplateCode
order by 1


Output from this query would look something like the following:

Blog 3
Central Admin 0
Invalid 0 Meeting 20
Team Collaboration Site 16
Wiki 4