Share via

SharePoint 2013: Important SQL Queries on SharePoint Content Databases – Part II

  • Article

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 it is strongly recommended you first go through it.

In this article, some more queries that are useful are included while working with SharePoint 2013 as follows:

Returns Documents Size based on Document Type

Query

 SELECT TOP 100 WEBS.FULLURL AS SITEURL, WEBS.TITLE AS [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 IS NOT NULL) AND (LEAFNAME <> ”) 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

 

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/1.png?w=800

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

Query

SELECT 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

https://howtodowithsharepoint.files.wordpress.com/2016/02/2.png?w=800

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

Query

SELECT 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

https://howtodowithsharepoint.files.wordpress.com/2016/02/3.png?w=800

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

Query

SELECT 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

https://howtodowithsharepoint.files.wordpress.com/2016/02/4.png?w=800

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

Query

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

https://howtodowithsharepoint.files.wordpress.com/2016/02/5.png?w=800

Returns all the top-level site collections

Query

SELECT SITEID AS SITEGUID, ID AS WEBGUID, FULLURL AS URL, TITLE, AUTHOR, TIMECREATED FROM DBO.WEBS WHERE (PARENTWEBID IS NULL)

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/6.png?w=800

Returns all the child sites in a site collection

Query

SELECT SITEID AS SITEGUID, ID AS WEBGUID, FULLURL AS URL, TITLE, AUTHOR, TIMECREATED FROM DBO.WEBS WHERE (NOT (PARENTWEBID IS NULL))

 

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/7.png?w=800

Returns all the SharePoint groups in a site collection

Query

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

https://howtodowithsharepoint.files.wordpress.com/2016/02/8.png?w=800

Returns all the users in a site collection

Query

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

 

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/9.png?w=800

Returns all the members of the SharePoint Groups

Query

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

 

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/10.png?w=800

Returns all the sites where a specific feature is activated

Query

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

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′)

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/11.png?w=800

Returns all the users assigned to roles

Query

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

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/12.png?w=800

Returns all the SharePoint groups assigned to roles

Query

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

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/13.png?w=800

Returns list of unhosted pages in the SharePoint solution

Query

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 [DIRNAME] NOT LIKE (‘%LISTS/%’) AND [SETUPPATH] IS NOT NULL ORDER BY [PAGE URL]

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/14.png?w=800

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

Query

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

Output

https://howtodowithsharepoint.files.wordpress.com/2016/02/15.png?w=800

That is all for this article.

Hope you find it helpful.