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