Click here to Skip to main content
15,860,972 members
Articles / Web Development / ASP.NET
Article

Useful SQL Queries to Analyze and Monitor SharePoint Portal Solutions Usage

Rate me:
Please Sign up or sign in to vote.
4.89/5 (17 votes)
25 May 20063 min read 188.3K   49   21
Some useful SQL queries to analyze and monitor SharePoint Portal solutions usage.

Overview

Applies To:

  • Microsoft SharePoint Portal Server 2003
  • Microsoft Windows SharePoint Services
  • Microsoft® ASP. NET 1.x, 2.x
  • Microsoft® Visual Studio® .NET 2003

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 SharePoint solution 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 queries that 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):
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    select COUNT(*) from webs
    where fullurl  like '%sites%' AND fullUrl <> 
        'MySite'  AND fullUrl <> 'personal'
  • List of all list/document libraries and total items:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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:
    SQL
    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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Architect Version 1
Ireland Ireland
Adnan Ahmed is SharePoint Architect in Version 1(http://www.version1.com), the IT Consulting Company in Ireland and has involved with many large enterprises to help them realise real benefits of SharePoint 2007|2010.

SharePoint Architect | Blogger | IT Evangelist | MCPD SharePoint 2010 Developer| MCITP SharePoint Administrator 2010

Email: adnan.ahmed@live.ie
Owner: http://www.mossgurus.com
http://www.sp-blogs.com
Linked In Profile: http://www.linkedin.com/in/syedadnanahmed

My Blogs:
http://www.sp-blogs.com/blogs/adnan

Comments and Discussions

 
QuestionAbsolutely Brilliant Pin
Member 147639634-Mar-20 21:54
Member 147639634-Mar-20 21:54 
QuestionQuery with users and sites Pin
Alessandro Moneta26-Nov-14 21:40
Alessandro Moneta26-Nov-14 21:40 
QuestionSharepoint 2010 Pin
Tomcause29-Feb-12 22:57
Tomcause29-Feb-12 22:57 
Questionaverage size of Pin
bronyx8-Aug-11 5:17
bronyx8-Aug-11 5:17 
QuestionCant figure out where to write the Queries Pin
Member 80150154-Aug-11 5:44
Member 80150154-Aug-11 5:44 
GeneralCode revisited Pin
slutbert29-Jul-10 15:30
slutbert29-Jul-10 15:30 
GeneralQuery to get Workflow history Pin
Jeffrey Wojtasik24-Feb-10 5:27
Jeffrey Wojtasik24-Feb-10 5:27 
QuestionClone a ChildSite with SQL , isposible? Pin
LarsPl29-Jul-08 22:43
LarsPl29-Jul-08 22:43 
GeneralAwesome Pin
MoonTwo30-Jun-08 13:54
MoonTwo30-Jun-08 13:54 
GeneralFind all admins for sites/subsites in a site collection Pin
sarahsez11-Jan-08 11:40
sarahsez11-Jan-08 11:40 
GeneralError msg when running "Total size of all documents query" Pin
Stealthius19-Oct-07 5:26
Stealthius19-Oct-07 5:26 
GeneralAre the queries portable to Sharepoint 3.0 Pin
pdfr19-Oct-07 1:34
pdfr19-Oct-07 1:34 
QuestionMember site Pin
kenthansen13-Jun-07 3:23
kenthansen13-Jun-07 3:23 
QuestionVery good. One Small Suggestion... Pin
RK KL25-May-07 5:42
RK KL25-May-07 5:42 
Generalthanks! Pin
socr081928-Dec-06 5:46
socr081928-Dec-06 5:46 
GeneralVery Useful. Pin
Ashaman30-May-06 2:11
Ashaman30-May-06 2:11 
GeneralRe: Very Useful. Pin
Syed Adnan Ahmed30-May-06 10:18
Syed Adnan Ahmed30-May-06 10:18 
Thanx Ashaman, I will update my article and mention your name to give you credit.
Adnan

MCP, MCAD.NET, MCSD.NET
GeneralThis is not allowed on Large SBS Server with huge no of sites Pin
Moustafa Arafa29-May-06 21:02
Moustafa Arafa29-May-06 21:02 
GeneralRe: This is not allowed on Large SBS Server with huge no of sites Pin
Syed Adnan Ahmed30-May-06 10:19
Syed Adnan Ahmed30-May-06 10:19 
GeneralRe: This is not allowed on Large SBS Server with huge no of sites Pin
Shola Salako31-Jul-07 9:26
Shola Salako31-Jul-07 9:26 
GeneralRe: This is not allowed on Large SBS Server with huge no of sites Pin
EROLMVP5-Aug-08 21:16
EROLMVP5-Aug-08 21:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.