Inspecting The SharePoint Content Database
This is going to be the first post on a many to come about the SharePoint Content Database. There are some cases when we need to look into and read from the content databases.
NOTE: Never update any SharePoint database directly. Always use the SharePoint API (Object Model) for any updates.
We will begin with some of the basic tables and a very high level diagram on some of the relationships between them.
Features | Table that holds information about all the activated features for each site collection or site. |
Sites | Table that holds information about all the site collections for this content database. |
Webs | Table that holds information about all the specific sites (webs) in each site collection. |
UserInfo | Table that holds information about all the users for each site collection. |
Groups | Table that holds information about all the SharePoint groups in each site collection. |
Roles | Table that holds information about all the SharePoint roles (permission levels) for each site. |
AllLists | Table that holds information about lists for each site. |
GroupMembership | Table that holds information about all the SharePoint group members. |
AllUserData | Table that holds information about all the list items for each list. |
AllDocs | Table that holds information about all the documents (and all list items) for each document library and list. |
RoleAssignment | Table that holds information about all the users or SharePoint groups that are assigned to roles. |
SchedSubscriptions | Table that holds information about all the scheduled subscriptions (alerts) for each user. |
ImmedSubscriptions | Table that holds information about all the immediate subscriptions (alerts) for each user. |
Below is a very high level database diagram. This does not show all the relationships between all the tables.
There are a some tables left out of this post that we will look at in upcoming posts. Here are some common queries that we can run against the content databases.
– 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)
– 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))
– 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
– 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
– Query to get all the members of the SharePoint Groups
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
– 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 = '00BFEA71-D1CE-42de-9C63-A44004CE0104')
– 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
– 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
These are just some of the common queries that I have used against the content database. In upcoming post I will exam how the permissions are scoped and how we can determine where the permissions are assigned by looking further into the content database. I will also show you how you can look into the content database and see where all the event handlers are being used and what they are attached to.
Enjoy!!!