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

Comments

Post a Comment

Popular posts from this blog

Adding Contact Selector Control to InfoPath form

The Contact Selector control is an ActiveX control but it is a special cased control, in that it can also be used in InfoPath browser forms. To use this control there are specific steps that need to be taken – let’s take a look at those now. Step 1 : Steps to add Contact Selector control to your Controls Task Pane 1) From the Controls Task Pane click "Add or Remove Custom Controls" link 2) Click the Add button 3) Add Custom Control Wizard select "ActiveX control" and click Next 4) This will display a list of controls , select "Contact Selector" ,click Next 5) Select “Don’t include a .cab file” and click Next 6) For Binding Property select Value and click Next 7) From the Field or group type box choose Field or group (any data type) and click Finish 8) Click Close and then click OK Step 2: Create the data structure for the Contact Selector Control The Contact Selector control needs to have a specific data structure to work properly – this is documented on
Read more

Adding Validation to a Custom NewForm.aspx in SharePoint & Redirect

SharePoint has a built in function called "PreSaveAction" that can be invoked to process anything before the form should submit. So now we will create a submit button which calls “PresaveActon” function. Once users Submits the form, PreSaveAction is executed. If PreSaveAction is returns true it will commit and redirect. If the PreSaveAction returns false, then the commit will not happen nor the redirect. Input Button Code: Replace this code <SharePoint:SaveButton runat="server" ControlMode="New" id="savebutton1"/> with <input type="button" value="Submit" name="btnSave" onclick="javascript: if(PreSaveAction()) {ddwrt:GenFireServerEvent('__commit;__redirect={http://<Site>/ SitePages/ThankYou.aspx}')}" /> Add Script in NewCustom Form. Now edit New custom newform.aspx and add content editor webpart and with below script <script type="text/javascript
Read more

How to add to Sub-Sub Menu in SharePoint

Image
MOSS 2007 sites can display drop down menus in the tabbed navigation across the top. Unfortunately there is not an easy check box to act ivate this functionality; instead things just have to be set up the right way. If you want your MOSS site to show drop down menus, make sure the following is true or walk through the following steps: 1. From the root of the parent site (Home) choose to create a new site (Site 1). Once that site is created, you will be in that new site. From here choose to create a new page. 2. Once that is created,choose to create another new site (SubSite 1). Then create a new page in SubSite1 3. Your site structure should resemble like this (Pic Shown below) 4. For each site in the Navigation settings, both Show Subsites and Show Pages should be checked. a. Select Site Actions - Site Settings - Modify Navigation. b. Check Show subsites and Show pages in the first row. 5. The end resul
Read more