Online Training On SharePoint
                      

Tuesday 3 June 2008

Some Important Tables with SharePoint 2007

Here is the list of some important tables in SharePoint Content DB:
  • Sites: This Table holds information about all the site collections for this content database.
  • Webs: This Table holds information about all the specific sites (webs) in each site collection.
  • UserInfo: This Table holds information about all the users for each site collection.
  • Groups: This Table holds information about all the SharePoint groups in each site collection.
  • Roles: This Table holds information about all the SharePoint roles (permission levels) for each site.
  • AllLists: This Table holds information about lists for each site.
  • GroupMembership: This Table holds information about all the SharePoint group members.
  • AllUserData: This Table holds information about all the list items for each list.
  • AllDocs: This Table holds information about all the documents (and all list items) for each document library and list.
  • AllUserData: This table has all the metadata which user provides in a document library.
  • RoleAssignment: This Table holds information about all the users or SharePoint groups that are assigned to roles.
  • SchedSubscriptions: This Table holds information about all the scheduled subscriptions (alerts) for each user.
  • ImmedSubscriptions: This Table holds information about all the immediate subscriptions (alerts) for each user.

Some queries with these tables:

-- Query to get all the top level site collections

SELECT SiteId AS Siteid, Id AS Webid, FullUrl AS FURL, Title, Author, TimeCreated FROM dbo.Webs WHERE (ParentWebId IS NULL)

-- Query to get all the child sites in a site collection

SELECT SiteId AS Siteid, Id AS Webid, FullUrl AS FURl, 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 JOINdbo.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.TimeActivatedFROM dbo.Features INNER JOIN dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId AND dbo.Features.WebId = dbo.Webs.Id WHERE (dbo.Features.FeatureId = '00AFDA71-D2CE-42fg-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 JOINdbo.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


You can also find interesting to read Different Database created with SharePoint

1 comment:

Larry W. Virden said...

With regards to these tables I have 2 questions.
1. I am trying to identify the URL for 2 sites that are marked in the SSP SiteSynch table as not being synched currently. How can I go from the SiteID in that table to a name, I presume, in the appropriate content db?
2. I am also trying to identify a list of users in the userinfo tables within SharePoint whose Account property does not match their user name property. In our environment, most people's account and user name match. However, in cases where a user has changed their name, etc. there are entries in the site collection user info table that have not been updated to the new account name. I need some way to a) identify the records that are inconsistent and b) update those records.
The user info records ARE being updated via profile synch for all properties except Account.

Thank you

Related Posts with Thumbnails