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
- 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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
That is all for this article.
Hope you find it helpful.