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

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

1

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

2

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

3

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

4

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

5

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

6

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

7

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

8

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

9

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

10

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

11

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

12

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

13

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

14

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

15

That is all for this article.

Hope you find it helpful.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s