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

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.