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

In the previous article, “SharePoint 2013: Important SQL Queries on SharePoint Content Databases – Part I”  on SQL Queries for SharePoint, we have discussed some of the useful SharePoint Queries. This article is in continuation of Part I and if you haven’t gone through the first article I would strongly recommend you to first go through it.

In this article I have included some more queries that are useful while working with SharePoint 2013 as follows.

Returns Documents Size based on Document Type

Query

  1. SELECT    
  2. TOP 100 WEBS.FULLURL AS SITEURL,    
  3. WEBS.TITLE AS [TITLE],    
  4. DIRNAME + ‘ / ’ + LEAFNAME AS [DOCUMENT NAME],    
  5. CAST(    
  6. (    
  7. CAST(    
  8. CAST(    
  9. SIZE AS DECIMAL(10, 2)    
  10. )/ 1024 AS DECIMAL(10, 2)    
  11. )/ 1024    
  12. AS DECIMAL(10, 2)    
  13. AS “SIZE IN MB”    
  14. FROM    
  15. DOCS    
  16. INNER JOIN WEBS ON DOCS.WEBID = WEBS.ID    
  17. INNER JOIN SITES ON WEBS.SITEID = SITES.ID    
  18. WHERE    
  19. DOCS.TYPE <> 1     
  20.   AND (LEAFNAME IS NOT NULL)     
  21.   AND (LEAFNAME <> ”)     
  22.   AND (LEAFNAME NOT LIKE ‘ %.STP’)     
  23.   AND (LEAFNAME NOT LIKE ‘ %.ASPX’)     
  24.   AND (LEAFNAME NOT LIKE ‘ %.XFP’)     
  25.   AND (LEAFNAME NOT LIKE ‘ %.DWP’)     
  26.   AND (    
  27.     LEAFNAME NOT LIKE ‘ % TEMPLATE % ’    
  28.   )     
  29.   AND (LEAFNAME NOT LIKE ‘ %.INF’)     
  30.   AND (LEAFNAME NOT LIKE ‘ %.CSS’)     
  31. ORDER BY     
  32.   “SIZE IN MB” DESC    

Output


Returns Totals No. of Document of type (.Docx)

Query

  1. SELECT  
  2. COUNT(*) AS ‘ # OF .DOCX’ 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 ‘%.DOCX’) AND (LEAFNAME NOT LIKE ‘%TEMPLATE%’)  

Output



Returns Totals No. of Document of type (.PPTX)

Query

  1. SELECT  
  2. COUNT(*) AS ‘ # OF .PPTX’ 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 ‘%.PPTX’) AND (LEAFNAME NOT LIKE ‘%TEMPLATE%’)  

Output


Returns Totals No. of Document of type (.XLSX)

Query

  1. SELECT  
  2. COUNT(*) AS ‘ # OF .XLSX’ 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 ‘%.XLSX’) AND (LEAFNAME NOT LIKE ‘%TEMPLATE%’)  

Output


Returns Totals No. of Document of type (.HTML)

Query

  1. SELECT COUNT(*) AS ‘# OF .HTML’ 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 ‘%.HTML’) AND (LEAFNAME NOT LIKE ‘%TEMPLATE%’)  

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 the SharePoint groups in a site collection

Query

  1. SELECT  
  2. DBO.WEBS.SITEID,  
  3. DBO.WEBS.ID,  
  4. DBO.WEBS.FULLURL,  
  5. DBO.WEBS.TITLE,  
  6. DBO.GROUPS.ID AS EXPR1,  
  7. DBO.GROUPS.TITLE AS EXPR2,  
  8. DBO.GROUPS.DESCRIPTION  
  9. FROM  
  10. DBO.GROUPS  
  11. 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. DBO.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 the 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

Feature ID of Announcements List : ’00BFEA71-D1CE-42de-9C63-A44004CE0104′,

  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 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 the SharePoint groups assigned to 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 list of unhosted pages in the SharePoint solution

Query

  1. SELECT  
  2. WEBS.FULLURL AS SITEURL,  
  3. CASE WHEN [DIRNAME] = ” THEN ‘ / ’ + [LEAFNAME] ELSE ‘ / ’ + [DIRNAME] + ’ / ’ + [LEAFNAME] END AS [PAGE URL],  
  4. CAST(  
  5. (  
  6. CAST(  
  7. CAST(  
  8. SIZE AS DECIMAL(10, 2)  
  9. )/ 1024 AS DECIMAL(10, 2)  
  10. )/ 1024  
  11. AS DECIMAL(10, 2)  
  12. AS ‘FILE SIZE IN MB’  
  13. FROM  
  14. DOCS  
  15. INNER JOIN WEBS ON DOCS.WEBID = WEBS.ID  
  16. WHERE  
  17. [TYPE] = 0  
  18. AND [LEAFNAME] LIKE ‘ %.ASPX’  
  19. )  
  20. AND [DIRNAME] NOT LIKE (‘ % _CATALOGS /% ’)  
  21. AND [DIRNAME] NOT LIKE (‘ %/ FORMS’)  
  22. AND [DIRNAME] NOT LIKE (‘ % LISTS /% ’)  
  23. AND [SETUPPATH] IS NOT NULL  
  24. ORDER BY  
  25. [PAGE URL]  

Output


Returns list of Site Title and total number of users associated with it

Query

  1. SELECT  
  2. WEBS.FULLURL,  
  3. WEBS.TITLE,  
  4. COUNT(WEBMEMBERS.USERID) AS ‘TOTAL USER’  
  5. FROM  
  6. WEBS  
  7. INNER JOIN WEBMEMBERS ON WEBS.ID = WEBMEMBERS.WEBID  
  8. WHERE  
  9. FULLURL NOT LIKE ‘ % SITES % ’  
  10. AND FULLURL <> ‘MYSITE’   
  11.   AND FULLURL <> ‘PERSONAL’   
  12. GROUP BY   
  13.   WEBS.FULLURL,   
  14.   WEBS.TITLE   
  15. ORDER BY   
  16.   ‘TOTAL USER’ DESC  

Output


That is all for this article.

Hope you find it helpful.