SharePoint–Using SQL Queries to Help Admin a Farm

Despite Microsoft’s insistence that one should never query a content database directly, I still find that SQL is a powerful tool that allows me to quickly gain an understanding of how my farm, web applications, and sites are being utilized by the enterprise.  Until I get a tool that lets me do this in the Microsoft “recommended” manner, I’ll stick with them… just need to remember that when querying the data I really want to make sure that I always use dirty reads by utilizing the “with (nolock)” hint so I’m not locking the database during normal activity hours. 

I do agree with Microsoft’s recommendation that the content databases never have their schemas modified or views added to them, as Microsoft will from time to time during a SharePoint update move things around.  I’ve seen a series of views written by a so-called SharePoint consultant that were housed in a content database blown away during an update and causing all sorts of havoc with a production environment.  However, if you roll your own SQL Select statements or house your Views in a separate database (Generally I use the nomenclature “WSS_Views_* to indicate it is meant for views only) you should be relatively safe. 

Writing your own queries is relatively easy once you understand how Microsoft organizes the information in their SQL tables.  Some of the tables I find myself querying against on a regular basis are:

  • Docs
  • Docversions
  • Sites
  • Webs

Information pertaining to document libraries, list, area, and sites can easily be queries 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.

Here are some of my favorite queries that I find myself using on a regular basis.

List of sites and sub-sites ordered by number of users

select webs.FullUrl ,Webs.Title, COUNT(WebMembers.UserId) As ‘Total Users’
from Webs with (NOLOCK) INNER JOIN WebMembers with (NOLOCK)
ON Webs.Id = WebMembers.WebId
Group BY webs.FullUrl,  Webs.Title
Order By  ‘Total Users’ desc

Largest 100 documents

SELECT TOP 100
Webs.FullUrl As SiteUrl,
Webs.Title as ‘Website Title’, 
DirName + ‘/’ + LeafName AS ‘Document URL’,
(Size / 1024 /1024) as ‘Size in MB’
FROM Docs with (NOLOCK) INNER JOIN Webs with (NOLOCK) On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
ORDER BY size DESC

Top 100 Most Versioned Documents

SELECT TOP 100
Webs.FullUrl As SiteUrl,
Webs.Title,
DirName + ‘/’ + LeafName AS ‘Document URL’,
COUNT(Docversions.InternalVersion)AS ‘Total Versions’,
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 with (NOLOCK) INNER JOIN DocVersions with (NOLOCK) ON Docs.Id = DocVersions.Id
INNER JOIN Webs On Docs.WebId = Webs.Id
INNER JOIN Sites ON Webs.SiteId = Sites.Id
GROUP BY Webs.FullUrl, Webs.Title, DirName + ‘/’ + LeafName
ORDER BY ‘Total Versions’ desc, ‘Total Document Size (MB)’ desc

Total Number of Documents

SELECT COUNT(*)
FROM Docs with (NOLOCK) INNER JOIN Webs with (NOLOCK) 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 number of a specific kind of document (ID by extension, so replace .doc with extension you are looking for)

SELECT count(*)
FROM Docs with (NOLOCK) INNER JOIN Webs with (NOLOCK) 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 size of Documents

SELECT SUM(Size/1024/1024)
       AS  ‘Total Size in MB’
FROM Docs with (NOLOCK) INNER JOIN Webs with (NOLOCK) 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’)

2 thoughts on “SharePoint–Using SQL Queries to Help Admin a Farm”

  1. I am not a SharePoint administrator but rather the SQL Server DBA. I have not ever worked with the back-end of SharePoint until now. Our new SharePoint Administrator needs me to pull data for Site, Owner, OwnerEmail, SiteOwnerPhone, SubSite, SubSiteOwner, SubSiteOwnerEmail, SubSiteOwnerPhone, Path/URL, Title.

    I am not familiar enough with all the tables and fields and would appreciate some help. I found this query on the internet somewhere, but it is not all inclusive for what I have previously listed above:

    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, dbo.UserInfo.tp_Email, dbo.Perms.ScopeUrl

    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 INNER JOIN

    dbo.Perms on dbo.RoleAssignment.ScopeId = dbo.Perms.ScopeId

    where dbo.Roles.Title in (‘Full Control’, ‘Design’, ‘Limited Access’, ‘Approve’)
    order by scopeurl

    Thanks in advance for any help!

Leave a Reply