Determining Which Site Template Was Used
In an existing SharePoint environment, it can often be difficult to determine which site templates were used to generate the sites, especially if administrators have customized the pages or added and deleted lists, libraries, and Web Parts. A quick query to the Webs table of the content database will reveal template and configuration information that can be translated by reading the appropriate XML file contained in SharePoint's "12 hive" (i.e., \%SystemDrive%\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12 of the SharePoint server). This information will reveal the templates used for every site in the database. Although SharePoint's default behavior is to generate a separate content database for each web application, it’s possible to span a single web application across multiple content databases or combine multiple web applications into a single content database. The following query reveals template information for all sites in the content database, regardless of web application assignment:
SELECT Title, WebTemplate, ProvisionConfig
FROM [dbo].[Webs]
To determine the template of a particular site, use a WHERE clause to filter the site by Title, Description, or Site ID number, as the following command shows:
WHERE Title = SiteA
The Web Template ID number and Provision Configuration ID number returned by the query might not be recognizable at first. The more friendly text name of the template can be garnered from one of the web configuration XML files located in the 12 hive on the SharePoint server. For example, WSS 3.0 site templates are listed in the webtemp.xml file, while many of the MOSS 2007 templates are defined in the webtempsps.xml file. With some experience, you'll be able to identify the template by its ID and Configuration Option numbers and not need to read these XML files.
Collecting Column Definitions
Imagine a scenario in which you suspect various lists have columns that are too generous with their storage size and you need to quickly determine the construction of all columns from a particular list, library, or gallery to prove it. Visiting the properties of each column individually in the GUI is too time consuming, and writing code might be too complex. However, simply querying the AllLists table in the content database can reveal column information about any and all lists throughout your SharePoint environment. The AllLists table contains a row for each list, library, and gallery throughout the logical portion of SharePoint that uses the given content database. Of the many columns in this table, the tp_fields column (ntext data type) contains detailed information about all of the columns in that row's particular list, library, or gallery. You can use the following code to query the tp_fields column:
SELECT tp_fields
FROM [dbo].[AllLists]
Figure 2 shows the output from this command laid out like an XML file, with a separate tag for each column divulging details such as column type, whether a value is required, size limit, and default assignment.
However, finding the column of interest can be a bit difficult if the same column name is used in more than one list. It’s best, if possible, to isolate this query to a particular list, library, or gallery by using a WHERE clause to avoid misinformation. Also keep in mind that although the values in the tp_fields column look like XML, they are actually ntext strings, so if you need to extrapolate only one column's worth of information you'll need an expression such as substring().
Gathering Security Information
It might be valuable to know who is accessing the databases you support. Although SharePoint maintains its own security architecture, from an audit and logging perspective, it would be nice to quickly see the security principals and permission assignments SharePoint is using to grant access to the content database. For example, say you just want a quick and dirty list of all users who have access to the sites in SharePoint—not individual permission levels or anything, just a list of names. Executing the query
SELECT dbo.Webs.FullUrl, dbo.Webs.Title, dbo.UserInfo.tp_ID, dbo.UserInfo.tp_Title
FROM dbo.UserInfo JOIN dbo.Webs
ON dbo.UserInfo.tp_SiteID = dbo.Webs.SiteId
returns results similar to those shown in Figure 3.
There are many more queries that will retrieve information directly from SQL Server about your SharePoint environment. Once you become familiar with the tables from the content database listed in Table 1, you'll find all kinds of new ways to decipher the myriad of ID numbers throughout the rows to join descriptive titles from other tables and produce recognizable result sets.
A Word of Warning
The T-SQL statements we've looked at so far have all been relatively harmless. The true "moving parts" of a SharePoint content database are the stored procedures and functions that manipulate the table rows. Although it might be tempting to get underneath the hood of these objects, be aware that altering a stored procedure could cause otherwise dormant triggers to fire and disabling corruption to ensue. The stored procedures and functions shown in Table 2 are best left alone in production SharePoint environments.If you really want to see what a stored procedure is doing, consider scripting it to a new query window or file in SSMS.
Explore These Objects in a Test Environment
This article has shed a bit of light on the schema of the WSS content database and identified some objects ripe for querying. There’s much more to the SharePoint databases, far more than can be covered in a single article, but the objects outlined herein should give you a starting point. Exploring these objects via SQL Server should always be performed in a development or testing environment and never in production. A simple slip of a mouse click could render a stored procedure capable of corruption, so be careful. For more information about these and other SharePoint objects in SQL Server, see the WSS library available at msdn.microsoft.com.