What are valid values for tp_servertemplate and what do they map to?

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.

All lists use the LISTS table. The sp_servertemplate column can be used to find all lists of a particular type, etc. The list varies a little bit between versions of SharePoint. For instance there are new values for blog and wiki in MOSS and WSS3, but they do not exist in WSS2 and SPS. As far as I know the newer version of SharePoint simply add more values, they don't change existing ones.

The question quickly becomes, "What are valid values for tp_servertemplate and what do they map to?" Below you will find such a list of possible values and what they map to in a user friendly and human readable format.

This text is essentially the text from the Microsoft Windows SharePoint Services 3.0 SDK or the actual docs

Optional Integer. Provides a unique identifier for the list definition. This identifier must be unique within the feature, but need not be unique across all feature definitions or site definitions. Windows SharePoint Services 3.0 includes the following list types by default.

• 100 Generic list

• 101 Document library

• 102 Survey

• 103 Links list

• 104 Announcements list

• 105 Contacts list

• 106 Events list

• 107 Tasks list

• 108 Discussion board

• 109 Picture library

• 110 Data sources

• 111 Site template gallery

• 112 User Information list

• 113 Web Part gallery

• 114 List template gallery

• 115 XML Form library

• 116 Master pages gallery

• 117 No-Code Workflows

• 118 Custom Workflow Process

• 119 Wiki Page library

• 120 Custom grid for a list

• 130 Data Connection library

• 140 Workflow History

• 150 Gantt Tasks list

• 200 Meeting Series list

• 201 Meeting Agenda list

• 202 Meeting Attendees list

• 204 Meeting Decisions list

• 207 Meeting Objectives list

• 210 Meeting text box

• 211 Meeting Things To Bring list

• 212 Meeting Workspace Pages list

• 301 Blog Posts list

• 302 Blog Comments list

• 303 Blog Categories list

• 1100 Issue tracking

• 1200 Administrator tasks list

For custom list templates this attribute should be set to a number above 10000 to ensure that it does not conflict with any present or future Microsoft supplied list types.

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

select case tp_servertemplate when 100 then 'Generic list' when 101 then 'Document library' when 102 then 'Survey' when 103 then 'Links list' when 104 then 'Announcements list' when 105 then 'Contacts list' when 106 then 'Events list' when 107 then 'Tasks list' when 108 then 'Discussion board' when 109 then 'Picture library' when 110 then 'Data sources' when 111 then 'Site template gallery' when 112 then 'User Information list' when 113 then 'Web Part gallery' when 114 then 'List template gallery' when 115 then 'XML Form library' when 116 then 'Master pages gallery' when 117 then 'No-Code Workflows' when 118 then 'Custom Workflow Process' when 119 then 'Wiki Page library' when 120 then 'Custom grid for a list' when 130 then 'Data Connection library' when 140 then 'Workflow History' when 150 then 'Gantt Tasks list' when 200 then 'Meeting Series list' when 201 then 'Meeting Agenda list' when 202 then 'Meeting Attendees list' when 204 then 'Meeting Decisions list' when 207 then 'Meeting Objectives list' when 210 then 'Meeting text box' when 211 then 'Meeting Things To Bring list' when 212 then 'Meeting Workspace Pages list' when 301 then 'Blog Posts list' when 302 then 'Blog Comments list' when 303 then 'Blog Categories list' when 1100 then 'Issue tracking' when 1200 then 'Administrator tasks list' else 'Other' end 'ListType' from lists