Sharepoint Syllabus

Part 1 - Fundamentals
Content Management / Site Collection Administration


1. Sites, Lists, and Web Part Basics

* Navigation
* Site collection, sites, lists
* List columns and views
* Versioning
* Draft/publishing versioning model
* Email notifications and list emails
* Web parts
* Standard vs. published web part pages
* Content query web part
* Managing personal site and user profile
* Search syntax from end user perspective

2. Security: Authorization

* Authentication vs. authorization
* Site Level Security
o Permissions
o Permission levels
o Active Directory users and groups
o SharePoint groups
* List, folder list item security
* Security Principals
* Limitations

3. Office Integration

* Opening items in Outlook (offline)
o Calendar
o Tasks
o Contacts
o Document libraries
* InfoPath Form Publishing
* Importing lists from excel
* Excel Services and dashboard pages
* KPI (key performance indicator) lists

4. Advanced Document Management

* Site columns
o Creating/updating/propagating
o Scopes
* Content types
o Creating/updating/propagating
o Content types settings
* Information management policies
o Labeling
o Auditing
o Expiration
o Using with content types
* Workflow
o Out of box vs. SharePoint Designer vs. Visual Studio
o Standard workflow templates
o Association
o Initiation and status
o Relation to content types

5. Site collection administration

* Manage content and structure tool
* Recycle bin
* List and site templates
o Creating new
o List and site collection galleries
o Examining Microsoft templates
* Features from site administrator perspective
* Site directory and self-service site collection creation
* What SharePoint does not share between site collections
* Modifying site image and theme

6. SharePoint Designer

* Editing look and feel
o Customized/uncustomized pages
o Master/content pages
o Safe vs. application pages and problems that they cause
o CSS Stylesheets customization
* working with external data sources
o SQL databases
o Lists on other sites
o Data view configuration
* building workflows
o Creating new
o Examining workflows from Microsoft templates

7. Web Content Management

* Publishing infrastructure and publishing features
* Publishing content types
* Page layouts and fields controls
* Page layout association with content type
* Custom content types
* Using SharePoint Designer to modify page layouts
* Publishing process
* Customizing content query web part look and feel
* Customizing master pages

8. Final Project

* Set up of "ABC Company" site collection and navigation
* Secure "ABC Company" site collection
* Set up document management for "ABC Company"
o Document libraries
o Content types
o Document center site publishing



Part 2 - Administration
System Administration / Architecture


10. SharePoint Central Administration

* Topology
o Servers in farm
o Services on server
* Global configuration
* Application security
* Web application management
o What is a web application, application pool in IIS, SharePoint, impersonation vs. process account
o What is the basic database organization in SharePoint databases and why we need multiple content databases for the same web application
o Content database tables
o Create or extend or delete web application
o Remove SharePoint from IIS web site
o Assignment permissions to users for the entire web application
* SharePoint Site Management
o Create and delete site collections
* Intranet/extranet deployment scenarios
* Shared services provider configuration

11. Alternative Access Mappings

* Full URL rendering
* Zones, public URLs, internal URLs
* Authentication considerations
* Load balancing considerations

12. Shared Services: Administering User Profiles, Audiences, and Personal Sites

* User profiles
o Import from Active Directory
o Creating new properties
* Audiences
o Creating audiences
o Using audiences in pages, web parts, and list items

13. Shared Services: Administering and Configuring Search

* Search architecture
* Content sources and schedules
* Crawl rules and security
* Properties and scopes
* Relevance inclusions / keywords / logging
* Customizing search center and web parts

14. Site Definitions and Features Architectural

* What has changed
* New feature architecture
* How to use features as administrators
* Simple feature
* Solutions

15. Single Sign-On Administration

* Creating applications definitions
* Using single sign on credentials in SharePoint Designer

16. Business Data Catalog Administration

* Lob systems, entities, properties, and methods
* Authorization
* Delegation vs. RevertToSelf authentication

17. Backup and Restore

* What to backup
* Backup method descriptions and limitations
* MOSS backup and restore tool through user interface and stsadm command-line
* Individual site collection backup and restore through stsadm command-line
* Third party solutions

18. Upgrading from 2003

* Basics of upgrade processes
* Step by step process of manual content database migration

19. Quick Deployment Guide

* Initial planning: define the team and the scope for the first stage of deployment
* Pilot deployment as an intranet: focus on collaboration, document management and portal features
* Test deployment on production hardware: plan capacity and figure out server farm structure
* Production deployment
* Beyond the initial stages

20. Basic SharePoint Installation

* Examining prerequisites
o Application server role
o Domain controller role
o SQL server
o Email
* Installing SharePoint files and central administration site
o Initial parameters
o Using the wizard
o Browsing through central administration iste
* Configuring services
* Creating shared services provider
* Creating end-user web application and a site collection
* Configuring Shared Services provider
o User profiles
o Search
o Business data catalog
o Excel services


Part 3 - Development Outline
Developement / Architecture


21. SharePoint API and development in ASP.NET Application

* Site collection organization
* List data manipulation
* Security
* Global Administration

22. Impersonation

* ASP.NET impersonation basics
* Using SharePoint impersonation api

23. Master Pages and CAML in the object model

* Examining the default master page for SharePoint
* Creating new Master Pages
* Using SPGrid and other SharePoint controls
* Using SPSiteData

24. Web parts

* Basic web part development in SharePoint 2007
* Web part class details
* Editors
* CreateChildControls
* User controls
* Web part manager
* Deployment
* Security

25. Event Handlers

* Synchronous
* Asynchronous
* Deployment

26. Site Definitions and Features Developmentn

* Visual Studio project structure for feature development
* Fields feature
* Content type features
* List feature
* List feature activation logic
* Custom forms for a content type
* Changing the item template for a custom form
* Solution deployment: building a solution package

27. Custom Search Queries

* Building a custom search query in a web part
* Advantages over using scopes

28. Business Data Catalog Development and Single Sign On

* Business data catalog architecture
* LOB (Line of Business) System objects, entities, identifiers, methods
* Using business data catalog API and writing custom web parts

29. Custom Workflow

* Using Visual Studio 2005 to build workflows
* Building custom workflow with InfoPath forms
* Workflow api

MOSS 2007 Design Component Relationships and Diagrams

MOSS 2007 Design Component Relationships and Diagrams

This article refers to Microsoft Office SharePoint Server 2007 (MOSS 2007) Beta 2 Tech Refresh.   Details are subject to change in the RTM version.

One of the MOSS 2007 buzzwords is master pages. But what all does that entail? How does the master page work with the content in the site and how do we do things like add web part zones and field controls? To aid and benefit SharePoint designers and those of us assigned to user interface customization, here is a break down of the relationships between master pages, page layouts, controls and content types. This is not a technical breakdown with sample code, this is just to explain the relationships between these core concepts in MOSS 2007.

Master Pages

Master pages are not a new concept or feature unique to SharePoint. Master pages were introduced with ASP.NET 2.0 as a way to centrally house and control the layout and design of a web site or application. A single master page file can control the look and feel of countless pages within a site/application. It is basically a next generation approach of how to include common content in lieu of frames or server side include files.

There is a lot of documentation already out there about master pages, and much of the knowledge can easily be utilized for developing and implementing master page files for SharePoint.

Content Pages

Content pages is the other half of master pages. Master pages store the layout and design, while content pages define the content. The content page is bound to a master page. Together the two create the presentation layer of content for a site/application.

In SharePoint content pages are called Page Layouts. But they work similar to content pages. The master page file is combined with the page layout to create the presentation layer of content for the SharePoint site.

Master Page and Page Layout combine to create the rendered page

Master Page/Content Page Resources

• ASP.NET Master Pages Overview
• Introducing ASP.NET 2.0 Master Pages

The Components of a SharePoint Master Page File

The master page will contain all of the user interface layout code for the site. This includes CSS, JavaScript and HTML. Examples of what you would put in the master page:

• Header code including company logo and branding images
• Navigation
• Footer code including copyright statements and links
• CSS (cascading style sheet) styles
• Body background colors, images or styles
• Common JavaScript functions

The other core component that is used in a master page file are the Content Placeholders. A Content Placeholder is just that, a location flagged as where content will be inserted. The content is stored in the page layouts. The content placeholders designate where the content from the page layout will be inserted in the master page. So for example, a simple master page could flow like this:

1. Registry tags, opening HTML/Body tags
2. CSS Styles
3. JavaScript Functions
4. HTML code for the header
5. Content Placeholder for the main content of the page
6. HTML code for the footer
7. Closing Body/HTML tags

A Content Placeholder looks like this:
<asp:ContentPlaceHolder id="MyPlaceholder" runat="server" />

Content Placeholders can be wrapped in HTML code, for example DIV, SPAN or TABLE tags. A good way to think about it is everywhere you would place content that you would like to have customized for each page, insert a Content Placeholder.

There are a set of required Content Placeholders for SharePoint. Without these placeholders, the site will not work. If you don't want to display all of the placeholders on your site, you can hide the placeholders by grouping them in a hidden ASP:Panel control, or individually set each placeholder with a visibility of false (visible="false").

Nesting and components of a Master Page File

For a full list of the content placeholders used in SharePoint, refer to the SDK (list to be included on this site in the future). For the base code including the bare minimum needed for a SharePoint master page, refer to this article:

How to: Create a Minimal Master Page

The Components of a Page Layout File

The page layout contains all of the Content Controls that match the content placeholders from the master page file. For example, the matching content control from our sample master page content placeholder from above would look like this:

<asp:Content ContentPlaceholderID="MyPlaceholder" runat="server">
        Insert field controls, web part zones or HTML code here
  </asp:Content>

Page layout content should be content that will change from page to page, for example:

• Title, Author, Byline
• Body Text

With the exception of the Registry tags, nothing can be outside of a content control in the page layout file! If you try to wrap HTML or add extra code outside of the content control, it will break the page.

SharePoint content is added to the content controls in a page layout in one of two ways, Field Controlsor Web Part Zones.

Field Controls are content areas that map to columns in the Content Type. Field control placement is controlled in the page layout file and can't be moved by the content editor through the web interface. They are ideal for situations where excerpts of content need to remain in a fixed location on a site. A field control looks similar to this:

<SharePointWebControls:TextField FieldName="ArticleByLine" runat="server"/>

Web Part Zones on the other hand allow content editors to add and move content around on the page. They work very similar to how they did in SharePoint 2003. The web part zone is specified in the page layout, and then the content editor can choose to add, remove or rearrange web parts within the specified zones. A web part zone looks similar to this:

<WebPartPages:WebPartZone runat="server" AllowPersonalization="false" FrameType="TitleBarOnly" ID="MiddleRightZone" Title="Middle Right Zone" Orientation="Vertical"><ZoneTemplate></ZoneTemplate></WebPartPages:WebPartZone>

Nesting and components of a Page Layout File

Content Types

Content Types are a new feature/concept of SharePoint 2007. They definitely warrant learning about and gaining an understanding of. A content type is a collection of settings that is applied to a particular category of content that can be reused multiple times. Through content types you can manage metadata and the behavior of a document or item type in a central, reusable way. Please refer to the following additional resources for a more in depth explanation of content types:

• Introduction to Content Types
• Plan content types [Office SharePoint Server]
• What Are Content Types, Anyway?

Columns from a content type are referenced through the field controls in the page layout. One content type can be reference by multiple page layouts, but a page layout can only reference one content type. This allows you to easily change the page layout of a page in a site without compromising the content since the content is controlled in the content type. The only restriction is to change the page layout to another page layout that is based on the same content type as the original.

Relationship between Content Types and Page Layouts, shown here wrapped with Master Pages. Click for a larger view.

A Few More Core Concepts and We Are Done…

Master Page Inheritance

Master page files can be applied on a site level, but not on a page level. So a sub site can have a different master page specified than the parent, or it can inherit the master page file settings from the parent.

Master page inheritance among sites and sub sites.

The Pages Library

When you create a page on a site, the page is stored in a Pages document library in the content database. A physical file is not created on the web server. The Page settings specify a page layout that can be changed to another page layout (that uses the same content type). Pages can be moved between Page libraries in a site collection. Each site has it's own Pages library. Because the Pages are stored in a library, they can have version history, check in-check out, publishing and workflow capabilities.

Pages library relationships with sites. 

The Silo Approach United

All of the components that contribute to the rendered SharePoint page is stored in silos... master page, page layout, page, content type and all the bits in between that link them together. To connect it all:

• A Page is stored in a Pages library within a site.
• The Page references a Content Type that helps populate the page with data. Content types are stored within a site.
• A Page Layout is applied to the Page to control what content appears and where through the use of Field Controls and Web Part Zones. This is specified within the Content Controls in the Page Layout.
• A Master Page is applied to the site to wrap on the look and feel and control content placement from the Page Layout through the use of Content Placeholders.

A snapshot of the silos layered together to create the rendered page...

Inspecting The SharePoint Content Database

Inspecting The SharePoint Content Database

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.

Useful SQL Queries to Analyze and Monitor SharePoint Portal Solutions Usage

Summary

It is a very common requirement in SharePoint Portal Solution to analyze a SharePoint solution in terms of storage capacity and statistics of sites, document libraries, area etc., to monitor the growth of the SharePointsolution and to take prompt action, if any thing goes wrong. Like, SharePoint should know what document library is most used in terms of versions, or the number of documents uploaded.

Microsoft SharePoint Portal Solution database is Microsoft�s proprietary database, and it cannot be modified because of any problems with future service pack releases, but sometimes you can write SELECT queries that can give full information that may be useful for the SharePoint Administrator. I was looking for some queriesthat would help me to analyze a SharePoint solution, but unfortunately, didn�t find any article that gives a list of useful queries, so I started to write my own SQL queries.

Contents

• An Overview of the SharePoint Database Schema
• A List of SQL Queries

An Overview of the SharePoint Database Schema

In order to write queries against the SharePoint database schema, you should how Microsoft organizes information in SQL tables. Some common tables are:

• Docs
• Docversions
• Sites
• Webs

Information pertaining to document libraries, list, area, and sites can easily be fetched by joining these tables in the Portal_Site database. You can write a query against the PORTAL_Profile database to get useful information of user profiles etc.

A List of SQL Queries

• Top 100 documents in terms of size (latest version(s) only):
   Collapse

  SELECT TOP 100 Webs.FullUrl As SiteUrl, 
  Webs.Title 'Document/List Library Title', 
  DirName + '/' + LeafName AS 'Document Name',
  CAST((CAST(CAST(Size as decimal(10,2))/1024 As 
        decimal(10,2))/1024) AS Decimal(10,2)) AS 'Size in MB'
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')  
                 AND (LeafName NOT LIKE '%.aspx') 
                 AND (LeafName NOT LIKE '%.xfp') 
                 AND (LeafName NOT LIKE '%.dwp') 
                 AND (LeafName NOT LIKE '%template%') 
                 AND (LeafName NOT LIKE '%.inf') 
                 AND (LeafName NOT LIKE '%.css') 
  ORDER BY 'Size in MB' DESC

• Top 100 most versioned documents:
   Collapse

  SELECT TOP 100
  Webs.FullUrl As SiteUrl, 
  Webs.Title 'Document/List Library Title', 
  DirName + '/' + LeafName AS 'Document Name',
  COUNT(Docversions.version)AS 'Total Version',
  SUM(CAST((CAST(CAST(Docversions.Size as decimal(10,2))/1024 As 
     decimal(10,2))/1024) AS Decimal(10,2)) )  AS  'Total Document Size (MB)',
  CAST((CAST(CAST(AVG(Docversions.Size) as decimal(10,2))/1024 As 
     decimal(10,2))/1024) AS Decimal(10,2))   AS  'Avg Document Size (MB)'
  FROM Docs INNER JOIN DocVersions ON Docs.Id = DocVersions.Id 
     INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 
  AND (LeafName NOT LIKE '%.stp')  
  AND (LeafName NOT LIKE '%.aspx')  
  AND (LeafName NOT LIKE '%.xfp') 
  AND (LeafName NOT LIKE '%.dwp') 
  AND (LeafName NOT LIKE '%template%') 
  AND (LeafName NOT LIKE '%.inf') 
  AND (LeafName NOT LIKE '%.css') 
  GROUP BY Webs.FullUrl, Webs.Title, DirName + '/' + LeafName
  ORDER BY 'Total Version' desc, 'Total Document Size (MB)' desc

• List of unhosted pages in the SharePoint solution:
   Collapse

  select Webs.FullUrl As SiteUrl, 
  case when [dirname] = '' 
  then '/'+[leafname] 
  else '/'+[dirname]+'/'+[leafname] 
  end as [Page Url], 
  CAST((CAST(CAST(Size as decimal(10,2))/1024 As 
     decimal(10,2))/1024) AS Decimal(10,2))   AS  'File Size in MB'
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  where [type]=0 
  and [leafname] like ('%.aspx') 
  and [dirname] not like ('%_catalogs/%') 
  and [dirname] not like ('%/Forms') 
  and [content] is not null 
  and [dirname] not like ('%Lists/%') 
  and [setuppath] is not null 
  order by [Page Url];

• List of top level WSS sites and their total size, including child sites in the portal:
   Collapse

  select FullUrl As SiteUrl,  
  CAST((CAST(CAST(DiskUsed as decimal(10,2))/1024 As 
     decimal(10,2))/1024) AS Decimal(10,2))   AS  'Total Size in MB'
  from sites 
  Where FullUrl LIKE '%sites%' AND 
     fullUrl <> 'MySite' AND fullUrl <> 'personal'
  

• List of portal area and total number of users:
   Collapse

  select webs.FullUrl, Webs.Title, 
      COUNT(WebMembers.UserId) As 'Total User'
  from Webs INNER JOIN WebMembers 
  ON Webs.Id = WebMembers.WebId
  Where fullurl NOT like '%sites%' AND 
      fullUrl <> 'MySite' AND fullUrl <> 'personal'
  Group BY webs.FullUrl,  Webs.Title
  Order By  'Total User' desc

• List of top level and sub sites in the portal and the number of users:
   Collapse

  select  webs.FullUrl ,Webs.Title, COUNT(WebMembers.UserId) As 'Total User'
  from Webs INNER JOIN WebMembers 
  ON Webs.Id = WebMembers.WebId
  where fullurl  like '%sites%' AND fullUrl <> 'MySite' AND fullUrl <> 'personal'
  Group BY webs.FullUrl,  Webs.Title
  Order By  'Total User' desc

• List of all portal area:
   Collapse

  select Webs.FullUrl As [Site Url], 
  Title AS [Area Title]
  from Webs 
  Where fullurl NOT like '%sites%' AND fullUrl <> 
      'MySite' AND fullUrl <> 'personal'

• List of the total portal area:
   Collapse

  select COUNT(*)from Webs 
  Where fullurl NOT like '%sites%' AND 
      fullUrl <> 'MySite' AND fullUrl <> 'personal'

• List of all top level and sub sites in the portal:
   Collapse

  select Webs.FullUrl As [Site Url], 
  Title AS [WSS Site Title]
  from webs
  where fullurl  like '%sites%' AND fullUrl <> 
       'MySite' AND fullUrl <> 'personal'

• List of the total top level and sub sites in the portal:
   Collapse

  select COUNT(*) from webs
  where fullurl  like '%sites%' AND fullUrl <> 
      'MySite'  AND fullUrl <> 'personal'

• List of all list/document libraries and total items:
   Collapse

  select  
  case when webs.fullurl = '' 
  then 'Portal Site' 
  else webs.fullurl
  end as [Site Relative Url], 
  webs.Title As [Site Title], 
  case tp_servertemplate
  when 104 then 'Announcement'
  when 105 then 'Contacts'
  When 108 then 'Discussion Boards'
  when 101 then 'Docuemnt Library'
  when 106 then 'Events'
  when 100 then 'Generic List'
  when 1100 then 'Issue List'
  when 103 then 'Links List'
  when 109 then 'Image Library'
  when 115 then 'InfoPath Form Library'
  when 102 then 'Survey'
  when 107 then 'Task List'
  else 'Other' end as Type,
  tp_title 'Title', 
  tp_description As Description, 
  tp_itemcount As [Total Item]
  from lists inner join webs ON lists.tp_webid = webs.Id
  Where tp_servertemplate IN (104,105,108,101, 
      106,100,1100,103,109,115,102,107,120)
  order by tp_itemcount desc

  Note: the tp_servertemplate field can have the following values:
  • 104  = Announcement
  • 105 = Contacts List
  • 108 = Discussion Boards
  • 101 = Document Library
  • 106 = Events
  • 100 = Generic List
  • 1100 = Issue List
  • 103 = Links List
  • 109 = Image Library
  • 115 = InfoPath Form Library
  • 102 = Survey List
  • 107 = Task List
• List of document libraries and total items:
   Collapse

  select  
  case when webs.fullurl = '' 
  then 'Portal Site' 
  else webs.fullurl
  end as [Site Relative Url], 
  webs.Title As [Site Title], 
  lists.tp_title As Title,
  tp_description As Description, 
  tp_itemcount As [Total Item]
  from lists inner join webs ON lists.tp_webid = webs.Id
  Where tp_servertemplate = 101
  order by tp_itemcount desc

• List of image libraries and total items:
   Collapse

  select case when webs.fullurl = '' 
  then 'Portal Site' 
  else webs.fullurl
  end as [Site Relative Url], 
  webs.Title As [Site Title], 
  lists.tp_title As Title,
  tp_description As Description, 
  tp_itemcount As [Total Item]
  from lists inner join webs ON lists.tp_webid = webs.Id
  Where tp_servertemplate = 109  -- Image Library
  
  order by tp_itemcount desc

• List of announcement list and total items:
   Collapse

  select case when webs.fullurl = '' 
  then 'Portal Site' 
  else webs.fullurl
  end as [Site Relative Url], 
  webs.Title As [Site Title], 
  lists.tp_title As Title,
  tp_description As Description, 
  tp_itemcount As [Total Item]
  from lists inner join webs ON lists.tp_webid = webs.Id
  Where tp_servertemplate = 104  -- Announcement List
  
  order by tp_itemcount desc

• List of contact list and total items:
   Collapse

  select case when webs.fullurl = '' 
  then 'Portal Site' 
  else webs.fullurl
  end as [Site Relative Url], 
  webs.Title As [Site Title], 
  lists.tp_title As Title,
  tp_description As Description, 
  tp_itemcount As [Total Item]
  from lists inner join webs ON lists.tp_webid = webs.Id
  Where tp_servertemplate = 105   -- Contact List
  
  order by tp_itemcount desc

• List of event list and total items:
   Collapse

  select case when webs.fullurl = '' 
  then 'Portal Site' 
  else webs.fullurl
  end as [Site Relative Url], 
  webs.Title As [Site Title], 
  lists.tp_title As Title,
  tp_description As Description, 
  tp_itemcount As [Total Item]
  from lists inner join webs ON lists.tp_webid = webs.Id
  Where tp_servertemplate = 106   -- Event List
  
  order by tp_itemcount desc

• List of all tasks and total items:
   Collapse

  select  
  case when webs.fullurl = '' 
  then 'Portal Site' 
  else webs.fullurl
  end as [Site Relative Url], 
  webs.Title As [Site Title], 
  lists.tp_title As Title,
  tp_description As Description, 
  tp_itemcount As [Total Item]
  from lists inner join webs ON lists.tp_webid = webs.Id
  Where tp_servertemplate = 107  -- Task List
  
  order by tp_itemcount desc

• List of all InfoPath form library and total items:
   Collapse

  select  
  case when webs.fullurl = '' 
  then 'Portal Site' 
  else webs.fullurl
  end as [Site Relative Url], 
  webs.Title As [Site Title], 
  lists.tp_title As Title,
  tp_description As Description, 
  tp_itemcount As [Total Item]
  from lists inner join webs ON lists.tp_webid = webs.Id
  Where tp_servertemplate = 115  -- Infopath Library
  
  order by tp_itemcount desc

• List of generic list and total items:
   Collapse

  select  
  case when webs.fullurl = '' 
  then 'Portal Site' 
  else webs.fullurl
  end as [Site Relative Url], 
  webs.Title As [Site Title], 
  lists.tp_title As Title,
  tp_description As Description, 
  tp_itemcount As [Total Item]
  from lists inner join webs ON lists.tp_webid = webs.Id
  Where tp_servertemplate = 100  -- Generic List
  
  order by tp_itemcount desc

• Total number of documents:
   Collapse

  SELECT COUNT(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')  
                 AND (LeafName NOT LIKE '%.aspx') 
                 AND (LeafName NOT LIKE '%.xfp') 
                 AND (LeafName NOT LIKE '%.dwp') 
                 AND (LeafName NOT LIKE '%template%') 
                 AND (LeafName NOT LIKE '%.inf') 
                 AND (LeafName NOT LIKE '%.css')

• Total MS Word documents:
   Collapse

  SELECT count(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName LIKE '%.doc') 
    AND (LeafName NOT LIKE '%template%')

• Total MS Excel documents:
   Collapse

  SELECT count(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName LIKE '%.xls') 
      AND  (LeafName NOT LIKE '%template%')

• Total MS PowerPoint documents:
   Collapse

  SELECT count(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName LIKE '%.ppt') 
      AND  (LeafName NOT LIKE '%template%')

• Total TXT documents:
   Collapse

  SELECT count(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName LIKE '%.txt')  
      AND  (LeafName NOT LIKE '%template%')

• Total Zip files:
   Collapse

  SELECT count(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName LIKE '%.zip') 
      AND  (LeafName NOT LIKE '%template%')

• Total PDF files:
   Collapse

  SELECT count(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName LIKE '%.pdf') 
      AND  (LeafName NOT LIKE '%template%')

• Total JPG files:
   Collapse

  SELECT count(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName LIKE '%.jpg') 
                 AND  (LeafName NOT LIKE '%template%')

• Total GIF files:
   Collapse

  SELECT count(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName LIKE '%.gif') 
                 AND  (LeafName NOT LIKE '%template%')

• Total files other than DOC, PDF, XLS, PPT, TXT, Zip, ASPX, DEWP, STP, CSS, JPG, GIF:
   Collapse

  SELECT count(*)
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName NOT LIKE '%.pdf') 
  AND  (LeafName NOT LIKE '%template%')
  AND (LeafName NOT LIKE '%.doc')
  AND (LeafName NOT LIKE '%.xls')
  AND (LeafName NOT LIKE '%.ppt')
  AND (LeafName NOT LIKE '%.txt')
  AND (LeafName NOT LIKE '%.zip')
  AND (LeafName NOT LIKE '%.aspx')
  AND (LeafName NOT LIKE '%.dwp')
  AND (LeafName NOT LIKE '%.stp')
  AND (LeafName NOT LIKE '%.css')
  AND (LeafName NOT LIKE '%.jpg')  
  AND (LeafName NOT LIKE '%.gif')  
  AND (LeafName <>'_webpartpage.htm')

• Total size of all documents:
   Collapse

  SELECT SUM(CAST((CAST(CAST(Size as decimal(10,2))/1024 
         As decimal(10,2))/1024) AS Decimal(10,2))) 
         AS  'Total Size in MB'
  FROM     Docs INNER JOIN Webs On Docs.WebId = Webs.Id
  INNER JOIN Sites ON Webs.SiteId = SItes.Id
  WHERE
  Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp') 
                 AND (LeafName NOT LIKE '%.aspx') 
                 AND (LeafName NOT LIKE '%.xfp') 
                 AND (LeafName NOT LIKE '%.dwp') 
                 AND (LeafName NOT LIKE '%template%') 
                 AND (LeafName NOT LIKE '%.inf') 
                 AND (LeafName NOT LIKE '%.css') 
                 AND (LeafName <>'_webpartpage.htm')

Conclusion

I have demonstrated how easy it to analyze a SharePoint Portal solution. In the second part of the article, I will try to write queries on Users, User Profiles, Subscriber Lists, Site Groups, Cross Site Groups etc.