SharePoint 2010 - The content databases, tables and SQL queries

Today we will take a closer look at the content database associated with a SharePoint 2010 Web application and also discuss about some of the important tables, their relationships and some important SQL queries relating them.

The Content Database - Server
Open SQL Server Management Studio and connect to the DB Server configured for SharePoint 2010. You will see a number of databases as shown in the following figure. The databases for SharePoint internal configurations are highlight in red. While other databases are associated with each web application created in SharePoint server including Central Administration Site.

The Content Database - Associated Web application
Lets take an example of the web application("http://home:3000/") we create in the previous post to understand the content DB structure associated with it.

The Content DB associated with our web application is "WSS_Content_3000". As shown in the following figure a content db consist of numerous tables that are internal managed by SharePoint.

The Content Database - Tables
We will discuss a few important tables that exist in every content database.

1) Sites: Holds information about all the site collections for this content database.

2) Webs: Holds information about all the specific sites (webs) in each site collection.

3) Features: Holds information about all the activated features for each site collection or site.

4) UserInfo: Holds information about all the users for each site collection.

5) Groups: Holds information about all the SharePoint groups in each site collection.

6) Roles: Holds information about all the SharePoint roles (permission levels) for each site.

7) AllLists: Holds information about lists for each site.

8) GroupMembership: Holds information about all the SharePoint group members.

9) AllUserData: Holds information about all the list items for each list.

10) AllDocs: Holds information about all the documents (and all list items) for each document library and list.

11) RoleAssignment: Holds information about all the users or SharePoint groups that are assigned to roles.

12) SchedSubscriptions: Holds information about all the scheduled subscriptions (alerts) for each user.
13) ImmedSubscriptions: Holds information about all the immediate subscriptions (alerts) for each user.


The Content Database - Relationships
Following diagram shows the high level relationship between the tables in the content database.


The Content Database - Important SQL Queries
Following are some of the important sql queries that can be used to view the data in Content DB.


1) Query to get all the top level site collections
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (ParentWebId IS NULL)

2) Query to get all the child sites in a site collection
SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated
FROM dbo.Webs
WHERE (NOT (ParentWebId IS NULL))

3) Query to get all the SharePoint groups in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.Groups.ID AS Expr1, 
dbo.Groups.Title AS Expr2, dbo.Groups.Description
FROM dbo.Groups INNER JOIN
dbo.Webs ON dbo.Groups.SiteId = dbo.Webs.SiteId

4) Query to get all the users in a site collection
SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, dbo.UserInfo.tp_ID, 
dbo.UserInfo.tp_DomainGroup, dbo.UserInfo.tp_SiteAdmin, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Email
FROM dbo.UserInfo INNER JOIN
dbo.Webs ON dbo.UserInfo.tp_SiteID = dbo.Webs.SiteId

5) Query to get all the members of the SharePoint Groups
(i) All the members in all the SharePoint Groups in a web application.
SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.GroupMembership INNER JOIN
dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId INNER JOIN
dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

(ii)All the members in each SharePoint Group in a web application.
SELECT dbo.Groups.ID, dbo.Groups.Title, dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.GroupMembership with (nolock) 
INNER JOIN dbo.Groups with (nolock) 
ON dbo.GroupMembership.GroupId = Groups.ID 
INNER JOIN dbo.UserInfo 
ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID

6) Query to get all the sites where a specific feature is activated
SELECT dbo.Webs.Id AS WebGuid, dbo.Webs.Title AS WebTitle, dbo.Webs.FullUrl AS WebUrl, dbo.Features.FeatureId, 
dbo.Features.TimeActivated
FROM dbo.Features INNER JOIN
dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId AND dbo.Features.WebId = dbo.Webs.Id
WHERE (dbo.Features.FeatureId = '00DGEA71-E2FE-42de-9DF3-A44065BE0104')

7) Query to get all the users assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle, 
dbo.UserInfo.tp_Title, dbo.UserInfo.tp_Login
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND 
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID

8) Query to get all the SharePoint groups assigned to roles
SELECT dbo.Webs.Id, dbo.Webs.Title, dbo.Webs.FullUrl, dbo.Roles.RoleId, dbo.Roles.Title AS RoleTitle, 
dbo.Groups.Title AS GroupName
FROM dbo.RoleAssignment INNER JOIN
dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId AND 
dbo.RoleAssignment.RoleId = dbo.Roles.RoleId INNER JOIN
dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId AND dbo.Roles.WebId = dbo.Webs.Id INNER JOIN
dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId AND 
dbo.RoleAssignment.PrincipalId = dbo.Groups.ID

Note:- It is highly recommended that you never make any changes through SQL interface because improper use might crash SharePoint Site.