SharePoint 2013 - Important SQL Queries On SharePoint Content Databases - Part One

Since it is not recommended to undergo direct execution of SQL Queries on SharePoint content database, I would strictly recommend you to first go through one of my earlier articles “Executing Direct SQL Queries on SharePoint Content Databases: Is it a good idea???” talking about the recommended practices and consequences of direct execution.

With the assumption that you are already aware of the consequences of executing the direct SQL queries on SharePoint content databases, I would like to present the following important queries that can be used to retrieve SharePoint Vitals directly from the content databases, which would otherwise be very difficult to get from SharePoint Programming APIs.

These queries are tested with SharePoint 2013 and a few of them might not work with the earlier versions of SharePoint due to changes in database schema.

Returns Total Number of Site Collections in a Web Application

Query

  1. SELECT COUNT(*) AS ‘TOTAL SITE COLLECTION’ FROM SITES  
Output



Returns Root Site Title for each Site Collection available in WebApplication

Query
  1. SELECT TITLE AS ‘ROOT WEB TITLE’, SITES.ROOTWEBID, SITES.ID AS ‘SITE COLLECTION ID’ FROM WEBS INNER JOIN SITES ON WEBS.ID = SITES.ROOTWEBID  
Output



Returns Total Web Sites in Web Application

Query
  1. SELECT COUNT(*) As “Webs Count” FROM WEBS   
Output



Returns Site Title and Site Id

Query
  1. SELECT TITLE AS ‘SITE TITLE’,FULLURL, SITEID AS ‘SITE COLLECTION ID’ FROM WEBS ORDER BY SITEID   
Output



Returns Total number of Web Sites under each Site Collection

Query
  1. SELECT SITEID, COUNT(*) AS ‘TOTAL SUB SITES’ FROM WEBS INNER JOIN SITES ON SITES.ID = WEBS.SITEID GROUP BY SITEID  
Output



Returns total number of Websites under each Site Collection for ‘Doc Lib’ and ‘Form Lib’

Query
  1. SELECT  
  2. “TEMPLATE TYPE” = CASE WHEN [LISTS].[TP_SERVERTEMPLATE] = 101 THEN ‘DOC LIB’ WHEN [LISTS].[TP_SERVERTEMPLATE] = 115 THEN ‘FORM LIB’ ELSE ‘UNKNOWN’ END,  
  3. “LIST URL” = ‘http :// win - etmg052h5r / ’;  
  4. CASE WHEN [WEBS].[FULLURL] = ” THEN [WEBS].[FULLURL] + [LISTS].[TP_TITLE] ELSE [WEBS].[FULLURL] + ‘ / ’ + [LISTS].[TP_TITLE] END,  
  5. “TEMPLATE URL” = ‘http :// win - etmg052h5r / ’;  
  6. + [DOCS].[DIRNAME] + ‘ / ’ + [DOCS].[LEAFNAME]  
  7. FROM  
  8. [LISTS]  
  9. LEFT OUTER JOIN [DOCS] ON [LISTS].[TP_TEMPLATE] = [DOCS].[ID],  
  10. [WEBS]  
  11. WHERE  
  12. (  
  13. [LISTS].[TP_SERVERTEMPLATE] = 101  
  14. OR [LISTS].[TP_SERVERTEMPLATE] = 115  
  15. )  
  16. AND [LISTS].[TP_WEBID] = [WEBS].[ID]  
  17. ORDER BY  
  18. “LIST URL”  
Output



Returns count of documents from site collection

Query
  1. SELECT SUM(ITEMCOUNT) AS [TOTAL ITEM] FROM LISTS INNER JOIN WEBS ON LISTS.TP_WEBID = WEBS.ID INNER JOIN ALLLISTSAUX ON LISTS.TP_ID = ALLLISTSAUX.LISTID WHERE TP_SERVERTEMPLATE = 101  
Output



Returns items count for each Document Library (101)

Query
  1. SELECT  
  2. CASE WHEN WEBS.FULLURL = ” THEN ‘PORTAL SITE’ ELSE WEBS.FULLURL END AS [SITE RELATIVE URL],  
  3. WEBS.TITLE AS [SITE TITLE],  
  4. LISTS.TP_TITLE AS TITLE,  
  5. TP_DESCRIPTION AS DESCRIPTION,  
  6. ITEMCOUNT AS [TOTAL ITEM]  
  7. FROM  
  8. LISTS  
  9. INNER JOIN WEBS ON LISTS.TP_WEBID = WEBS.ID  
  10. INNER JOIN ALLLISTSAUX ON LISTS.TP_ID = ALLLISTSAUX.LISTID  
  11. WHERE  
  12. TP_SERVERTEMPLATE = 101  
  13. ORDER BY  
  14. [SITE RELATIVE URL]  
Output



Returns all the top level site collections

Query
  1. SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated FROM dbo.Webs WHERE (ParentWebId IS NULL)   
Output



Returns all the child sites in a site collection

Query
  1. SELECT SiteId AS SiteGuid, Id AS WebGuid, FullUrl AS Url, Title, Author, TimeCreated FROM dbo.Webs WHERE (NOT (ParentWebId IS NULL))  
Output



Returns all SharePoint groups in a site collection

Query
  1. 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   
Output



Returns all the users in a site collection

Query
  1. SELECT  
  2. dbo.Webs.SiteId,  
  3. dbo.Webs.Id,  
  4. dbo.Webs.FullUrl,  
  5. dbo.Webs.Title,  
  6. bo.UserInfo.tp_ID,  
  7. dbo.UserInfo.tp_DomainGroup,  
  8. dbo.UserInfo.tp_SiteAdmin,  
  9. dbo.UserInfo.tp_Title,  
  10. dbo.UserInfo.tp_Email  
  11. FROM  
  12. dbo.UserInfo  
  13. INNER JOIN dbo.Webs ON dbo.UserInfo.tp_SiteID = dbo.Webs.SiteId  
Output



Returns all the members of SharePoint groups

Query
  1. SELECT  
  2. dbo.Groups.ID,  
  3. dbo.Groups.Title,  
  4. dbo.UserInfo.tp_Title,  
  5. dbo.UserInfo.tp_Login  
  6. FROM  
  7. dbo.GroupMembership  
  8. INNER JOIN dbo.Groups ON dbo.GroupMembership.SiteId = dbo.Groups.SiteId  
  9. INNER JOIN dbo.UserInfo ON dbo.GroupMembership.MemberId = dbo.UserInfo.tp_ID  
Output



Returns all the sites where a specific feature is activated

Query
  1. SELECT  
  2. dbo.Webs.Id AS WebGuid,  
  3. dbo.Webs.Title AS WebTitle,  
  4. dbo.Webs.FullUrl AS WebUrl,  
  5. dbo.Features.FeatureId,  
  6. dbo.Features.TimeActivated  
  7. FROM  
  8. dbo.Features  
  9. INNER JOIN dbo.Webs ON dbo.Features.SiteId = dbo.Webs.SiteId  
  10. AND dbo.Features.WebId = dbo.Webs.Id  
  11. WHERE  
  12. (  
  13. dbo.Features.FeatureId = ’00BFEA71 - D1CE - 42de - 9C63 - A44004CE0104′  
  14. )  
Output



Returns all the users assigned to the roles

Query
  1. SELECT  
  2. dbo.Webs.Id,  
  3. dbo.Webs.Title,  
  4. dbo.Webs.FullUrl,  
  5. dbo.Roles.RoleId,  
  6. dbo.Roles.Title AS RoleTitle,  
  7. dbo.UserInfo.tp_Title,  
  8. dbo.UserInfo.tp_Login  
  9. FROM  
  10. dbo.RoleAssignment  
  11. INNER JOIN dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId  
  12. AND dbo.RoleAssignment.RoleId = dbo.Roles.RoleId  
  13. INNER JOIN dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId  
  14. AND dbo.Roles.WebId = dbo.Webs.Id  
  15. INNER JOIN dbo.UserInfo ON dbo.RoleAssignment.PrincipalId = dbo.UserInfo.tp_ID  
Output



Returns all SharePoint groups assigned to the roles

Query
  1. SELECT  
  2. dbo.Webs.Id,  
  3. dbo.Webs.Title,  
  4. dbo.Webs.FullUrl,  
  5. dbo.Roles.RoleId,  
  6. dbo.Roles.Title AS RoleTitle,  
  7. dbo.Groups.Title AS GroupName  
  8. FROM  
  9. dbo.RoleAssignment  
  10. INNER JOIN dbo.Roles ON dbo.RoleAssignment.SiteId = dbo.Roles.SiteId  
  11. AND dbo.RoleAssignment.RoleId = dbo.Roles.RoleId  
  12. INNER JOIN dbo.Webs ON dbo.Roles.SiteId = dbo.Webs.SiteId  
  13. AND dbo.Roles.WebId = dbo.Webs.Id  
  14. INNER JOIN dbo.Groups ON dbo.RoleAssignment.SiteId = dbo.Groups.SiteId  
  15. AND dbo.RoleAssignment.PrincipalId = dbo.Groups.ID  
Output



Returns all the users assigned to the roles

Query
  1. SELECT  
  2. DISTINCT CASE WHEN PATINDEX(‘ % \ % ’, FullUrl) > 0 THEN LEFT(  
  3. FullUrl,  
  4. PATINDEX(‘ % \ % ’, FullUrl) – 1  
  5. ELSE FullUrl END AS [Site],  
  6. Webs.Title,  
  7. Webs.FullUrl,  
  8. Perms.ScopeUrl,  
  9. UserInfo.tp_Login As Account,  
  10. CASE WHEN UserInfo.tp_DomainGroup > 0 THEN NULL ELSE UserInfo.tp_Title END AS Username,  
  11. CASE WHEN UserInfo.tp_DomainGroup > 0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group],  
  12. NULL AS [SharePoint Group],  
  13. Roles.Title AS RoleTitle,  
  14. Roles.PermMask  
  15. FROM  
  16. dbo.RoleAssignment  
  17. INNER JOIN dbo.UserInfo ON RoleAssignment.SiteId = UserInfo.tp_SiteID  
  18. AND UserInfo.tp_ID = RoleAssignment.PrincipalId  
  19. INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId  
  20. AND Perms.ScopeId = RoleAssignment.ScopeId  
  21. INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId  
  22. AND RoleAssignment.RoleId = Roles.RoleId  
  23. INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId  
  24. AND Roles.WebId = Webs.Id  
  25. WHERE  
  26. Roles.Type <> 1   
  27.   AND tp_Deleted = 0  
Output



Returns all the SharePoint groups assigned to the roles

Query
  1. SELECT  
  2. DISTINCT CASE WHEN PATINDEX(‘ % \ % ’, FullUrl) > 0 THEN LEFT(  
  3. FullUrl,  
  4. PATINDEX(‘ % \ % ’, FullUrl) – 1  
  5. ELSE FullUrl END AS [Site],  
  6. Webs.Title,  
  7. Webs.FullUrl,  
  8. Perms.ScopeUrl,  
  9. UserInfo.tp_Login As Account,  
  10. CASE WHEN UserInfo.tp_DomainGroup > 0 THEN NULL ELSE UserInfo.tp_Title END AS Username,  
  11. CASE WHEN UserInfo.tp_DomainGroup > 0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group],  
  12. Groups.Title AS [SharePoint Group],  
  13. Roles.Title AS RoleTitle,  
  14. Roles.PermMask  
  15. FROM  
  16. dbo.RoleAssignment  
  17. INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId  
  18. AND RoleAssignment.RoleId = Roles.RoleId  
  19. INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId  
  20. AND Perms.ScopeId = RoleAssignment.ScopeId  
  21. INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId  
  22. AND Roles.WebId = Webs.Id  
  23. INNER JOIN dbo.Groups ON RoleAssignment.SiteId = Groups.SiteId  
  24. AND RoleAssignment.PrincipalId = Groups.ID  
  25. INNER JOIN dbo.GroupMembership ON GroupMembership.SiteId = Groups.SiteId  
  26. AND GroupMembership.GroupId = Groups.ID  
  27. INNER JOIN dbo.UserInfo ON GroupMembership.SiteId = UserInfo.tp_SiteID  
  28. AND GroupMembership.MemberId = UserInfo.tp_ID  
  29. WHERE  
  30. Roles.Type <> 1   
  31.   AND tp_Deleted = 0  
Output



Returns all the documents from all the lists available in WebApplication

Query
  1. SELECT  
  2. AllDocs.Leafname AS FileName,  
  3. AllDOcs.Dirname AS “Folder Path”,  
  4. AllLists.tp_Title AS “List Title”,  
  5. Webs.Title AS “Web Title”  
  6. FROM  
  7. AllDocs  
  8. JOIN AllLists ON AllLists.tp_Id = AllDocs.ListId  
  9. JOIN Webs ON Webs.Id = AllLists.tp_WebId  
  10. ORDER BY  
  11. webs.title  
Output



Returns master pages in Web Application for all the Websites

Query
  1. SELECT  
  2. AllDocs.Leafname AS FileName,  
  3. AllDocs.Dirname AS “Folder Path”,  
  4. AllLists.tp_Title AS “List Title”,  
  5. Webs.Title AS “Web Title”  
  6. FROM  
  7. AllDocs  
  8. JOIN AllLists ON AllLists.tp_Id = AllDocs.ListId  
  9. JOIN Webs ON Webs.Id = AllLists.tp_WebId  
  10. WHERE  
  11. AllDocs.Extension = ‘master’  
  12. ORDER BY  
  13. Webs.Title  
Output



Returns top 100 documents that are versioned and is based on doc size

Query
  1. SELECT  
  2. TOP 100 Webs.FullUrl As SiteUrl,  
  3. Webs.Title ‘Document / List Library Title’,  
  4. DirName + ‘ / ’ + LeafName AS ‘Document Name’,  
  5. COUNT(AllDocversions.UIVersion) AS ‘Total Version’,  
  6. SUM(  
  7. CAST(  
  8. (  
  9. CAST(  
  10. CAST(  
  11. AllDocversions.Size as decimal(10, 2)  
  12. )/ 1024 As decimal(10, 2)  
  13. )/ 1024  
  14. AS Decimal(10, 2)  
  15. )  
  16. AS ‘Total Document Size (MB) ’,  
  17. CAST(  
  18. (  
  19. CAST(  
  20. CAST(  
  21. AVG(AllDocversions.Sizeas decimal(10, 2)  
  22. )/ 1024 As decimal(10, 2)  
  23. )/ 1024  
  24. AS Decimal(10, 2)  
  25. AS ‘Avg Document Size (MB) ’  
  26. FROM  
  27. Docs  
  28. INNER JOIN AllDocversions ON Docs.Id = AllDocversions.Id  
  29. INNER JOIN Webs On Docs.WebId = Webs.Id  
  30. INNER JOIN Sites ON Webs.SiteId = SItes.Id  
  31. WHERE  
  32. Docs.Type <> 1   
  33.   AND (LeafName NOT LIKE ‘ %.stp’)   
  34.   AND (LeafName NOT LIKE ‘ %.aspx’)   
  35.   AND (LeafName NOT LIKE ‘ %.xfp’)   
  36.   AND (LeafName NOT LIKE ‘ %.dwp’)   
  37.   AND (  
  38.     LeafName NOT LIKE ‘ % template % ’  
  39.   )   
  40.   AND (LeafName NOT LIKE ‘ %.inf’)   
  41.   AND (LeafName NOT LIKE ‘ %.css’)   
  42. GROUP BY   
  43.   Webs.FullUrl,   
  44.   Webs.Title,   
  45.   DirName + ‘ / ’ + LeafName   
  46. ORDER BY   
  47.   ‘Total Version’ desc,   
  48.   ‘Total Document Size (MB) ’ desc  
Output



Returns the document List Name, File Name, URL and the content (Binary format)

Query
  1. SELECT  
  2. AllLists.tp_Title AS “List Name”,  
  3. AllDocs.LeafName AS “File Name”,  
  4. AllDocs.DirName AS “URL”,  
  5. DocStreams.Content AS “Document Contnt (Binary) ”  
  6. FROM  
  7. AllDocs  
  8. JOIN DocStreams ON AllDocs.Id = DocStreams.DocId  
  9. JOIN AllLists ON AllLists.tp_id = AllDocs.ListId  
Output



Returns documents by age

Query
  1. SELECT  
  2. Webs.FullUrl AS SiteUrl,  
  3. Webs.Title AS [Title],  
  4. DirName + ‘ / ’ + LeafName AS [Document Name],  
  5. Docs.TimeCreated  
  6. FROM  
  7. Docs  
  8. INNER JOIN Webs On Docs.WebId = Webs.Id  
  9. INNER JOIN Sites ON Webs.SiteId = Sites.Id  
  10. WHERE  
  11. Docs.Type <> 1   
  12.   AND (LeafName IS NOT NULL)   
  13.   AND (LeafName <> ”)   
  14.   AND (LeafName NOT LIKE ‘ %.stp’)   
  15.   AND (LeafName NOT LIKE ‘ %.aspx’)   
  16.   AND (LeafName NOT LIKE ‘ %.xfp’)   
  17.   AND (LeafName NOT LIKE ‘ %.dwp’)   
  18.   AND (  
  19.     LeafName NOT LIKE ‘ % template % ’  
  20.   )   
  21.   AND (LeafName NOT LIKE ‘ %.inf’)   
  22.   AND (LeafName NOT LIKE ‘ %.css’)   
  23. ORDER BY   
  24.   Docs.TimeCreated DESC  
Output



Returns total # of documents

Query
  1. SELECT  
  2. COUNT(*) As “Total Number of Documents”  
  3. FROM  
  4. Docs  
  5. INNER JOIN Webs On Docs.WebId = Webs.Id  
  6. INNER JOIN Sites ON Webs.SiteId = Sites.Id  
  7. WHERE  
  8. Docs.Type <> 1   
  9.   AND (LeafName IS NOT NULL)   
  10.   AND (LeafName <> ”)   
  11.   AND (LeafName NOT LIKE ‘ %.stp’)   
  12.   AND (LeafName NOT LIKE ‘ %.aspx’)   
  13.   AND (LeafName NOT LIKE ‘ %.xfp’)   
  14.   AND (LeafName NOT LIKE ‘ %.dwp’)   
  15.   AND (  
  16.     LeafName NOT LIKE ‘ % template % ’  
  17.   )   
  18.   AND (LeafName NOT LIKE ‘ %.inf’)   
  19.   AND (LeafName NOT LIKE ‘ %.css’)  
Output


Returns total size of all content

Query
  1. SELECT  
  2. SUM(  
  3. CAST(  
  4. (  
  5. CAST(  
  6. CAST(  
  7. Size AS DECIMAL(10, 2)  
  8. )/ 1024 AS DECIMAL(10, 2)  
  9. )/ 1024  
  10. AS DECIMAL(10, 2)  
  11. )  
  12. AS “Total Size in MB”  
  13. FROM  
  14. Docs  
  15. INNER JOIN Webs ON Docs.WebId = Webs.Id  
  16. INNER JOIN Sites ON Webs.SiteId = Sites.Id  
  17. WHERE  
  18. Docs.Type <> 1   
  19.   AND (LeafName IS NOT NULL)   
  20.   AND (LeafName NOT LIKE ‘ %.stp’)   
  21.   AND (LeafName NOT LIKE ‘ %.aspx’)   
  22.   AND (LeafName NOT LIKE ‘ %.xfp’)   
  23.   AND (LeafName NOT LIKE ‘ %.dwp’)   
  24.   AND (  
  25.     LeafName NOT LIKE ‘ % template % ’  
  26.   )   
  27.   AND (LeafName NOT LIKE ‘ %.inf’)   
  28.   AND (LeafName NOT LIKE ‘ %.css’)  
Output



In the upcoming articles of this series, I will try to add some useful queries that can retrieve some of the other pieces of vital information out of SharePoint.

Hope you found it helpful.