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

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

Since it is not recommended to undergo direct execution of SQL Queries on SharePoint Content Database so I would strictly recommend you to first go through one of my earlier articles “Executing Direct SQL Queries on SharePoint Content Databases: Is it a good idea???” talking about recommended practices & consequences of direct execution.

With the assumption that you already aware of the consequences of executing the direct SQL queries on SharePoint Content Databases, I would like to present the following important queries that can be used to retrieve SharePoint Vitals directly from the Content Databases, which would otherwise be very difficult to get from SharePoint Programming APIs.

These Queries are tested with SharePoint 2013 and few of them might not work with the earlier Versions of SharePoint due to change in Database Schema.

Returns Total Number of Site Collections in a Web Application

Query

SELECT COUNT(*) AS ‘TOTAL SITE COLLECTION’ FROM SITES

Output

1

Returns Root Site Title for each Site Collection available in WebApplication

Query

SELECT TITLE AS ‘ROOT WEB TITLE’, SITES.ROOTWEBID, SITES.ID AS ‘SITE COLLECTION ID’ FROM WEBS INNER JOIN SITES ON WEBS.ID = SITES.ROOTWEBID

Output

2

Returns Total Web Sites in Web Application

Query

SELECT COUNT(*) As “Webs Count” FROM WEBS 

Output

3

Returns Site Title and Site Id

Query

SELECT TITLE AS ‘SITE TITLE’,FULLURL, SITEID AS ‘SITE COLLECTION ID’ FROM WEBS ORDER BY SITEID 

Output

4

Returns Total number of Web Sites under each Site Collection

Query

SELECT SITEID, COUNT(*) AS ‘TOTAL SUB SITES’ FROM WEBS INNER JOIN SITES ON SITES.ID = WEBS.SITEID GROUP BY SITEID

Output

5

Returns Total number of Web Sites under each Site Collection for ‘Doc Lib’ and ‘Form Lib’

Query

SELECT “TEMPLATE TYPE” = CASE WHEN [LISTS].[TP_SERVERTEMPLATE] = 101 THEN ‘DOC LIB’ WHEN [LISTS].[TP_SERVERTEMPLATE] = 115 THEN ‘FORM LIB’ ELSE ‘UNKNOWN’ END, “LIST URL” = ‘http://win-etmg052h5r/&#8217; + CASE WHEN [WEBS].[FULLURL]=” THEN [WEBS].[FULLURL] + [LISTS].[TP_TITLE] ELSE [WEBS].[FULLURL] + ‘/’ + [LISTS].[TP_TITLE] END, “TEMPLATE URL” = ‘http://win-etmg052h5r/&#8217; + [DOCS].[DIRNAME] + ‘/’ + [DOCS].[LEAFNAME] FROM [LISTS] LEFT OUTER JOIN [DOCS] ON [LISTS].[TP_TEMPLATE]=[DOCS].[ID], [WEBS] WHERE ([LISTS].[TP_SERVERTEMPLATE] = 101 OR [LISTS].[TP_SERVERTEMPLATE] = 115) AND [LISTS].[TP_WEBID]=[WEBS].[ID] ORDER BY “LIST URL”  

Output

 6

Returns count of documents from site collection

Query

SELECT SUM(ITEMCOUNT) AS [TOTAL ITEM] FROM LISTS INNER JOIN WEBS ON LISTS.TP_WEBID = WEBS.ID INNER JOIN ALLLISTSAUX ON LISTS.TP_ID = ALLLISTSAUX.LISTID WHERE TP_SERVERTEMPLATE = 101

Output

7

Returns Items Count for each Document Library (101)

Query

SELECT CASE WHEN WEBS.FULLURL = ” THEN ‘PORTAL SITE’ ELSE WEBS.FULLURL END AS [SITE RELATIVE URL], WEBS.TITLE AS [SITE TITLE],LISTS.TP_TITLE AS TITLE,TP_DESCRIPTION AS DESCRIPTION,ITEMCOUNT AS [TOTAL ITEM] FROM LISTS INNER JOIN WEBS ON LISTS.TP_WEBID = WEBS.ID INNER JOIN ALLLISTSAUX ON LISTS.TP_ID = ALLLISTSAUX.LISTID WHERE TP_SERVERTEMPLATE = 101 ORDER BY [SITE RELATIVE URL] 

Output

8

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

9

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

10

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

11

Returns all the users in a site collection

Query

SELECT dbo.Webs.SiteId, dbo.Webs.Id, dbo.Webs.FullUrl, dbo.Webs.Title, bo.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

12

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

13

Returns all the sites where a specific feature is activated

Query

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

14

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

15

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

16

Returns all the users assigned to roles

Query

SELECT DISTINCT CASE WHEN PATINDEX(‘%\%’, FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX(‘%\%’, FullUrl) – 1) ELSE FullUrl END AS [Site], Webs.Title, Webs.FullUrl,Perms.ScopeUrl,UserInfo.tp_Login As Account, CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username, CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group], NULL AS [SharePoint Group], Roles.Title AS RoleTitle, Roles.PermMask FROM dbo.RoleAssignment INNER JOIN dbo.UserInfo ON RoleAssignment.SiteId = UserInfo.tp_SiteID AND UserInfo.tp_ID = RoleAssignment.PrincipalId INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id WHERE Roles.Type<>1 AND tp_Deleted=0

Output

17

Returns all the SharePoint groups assigned to roles

Query

SELECT DISTINCT CASE WHEN PATINDEX(‘%\%’, FullUrl) > 0 THEN LEFT(FullUrl, PATINDEX(‘%\%’, FullUrl) – 1) ELSE FullUrl END AS [Site], Webs.Title, Webs.FullUrl, Perms.ScopeUrl, UserInfo.tp_Login As Account, CASE WHEN UserInfo.tp_DomainGroup>0 THEN NULL ELSE UserInfo.tp_Title END AS Username, CASE WHEN UserInfo.tp_DomainGroup>0 THEN UserInfo.tp_Login ELSE NULL END AS [AD Group], Groups.Title AS [SharePoint Group], Roles.Title AS RoleTitle, Roles.PermMask FROM dbo.RoleAssignment INNER JOIN dbo.Roles ON RoleAssignment.SiteId = Roles.SiteId AND RoleAssignment.RoleId = Roles.RoleId INNER JOIN dbo.Perms ON Perms.SiteId = RoleAssignment.SiteId AND Perms.ScopeId = RoleAssignment.ScopeId INNER JOIN dbo.Webs ON Roles.SiteId = Webs.SiteId AND Roles.WebId = Webs.Id INNER JOIN dbo.Groups ON RoleAssignment.SiteId = Groups.SiteId AND RoleAssignment.PrincipalId = Groups.ID INNER JOIN dbo.GroupMembership ON GroupMembership.SiteId = Groups.SiteId AND GroupMembership.GroupId = Groups.ID INNER JOIN dbo.UserInfo ON GroupMembership.SiteId = UserInfo.tp_SiteID AND GroupMembership.MemberId = UserInfo.tp_ID WHERE Roles.Type<>1 AND tp_Deleted=0

Output

18

Returns all document from all lists availabe in WebApplication

Query

SELECT AllDocs.Leafname AS FileName, AllDOcs.Dirname AS “Folder Path”, AllLists.tp_Title AS “List Title”, Webs.Title AS “Web Title” FROM AllDocs JOIN AllLists ON AllLists.tp_Id=AllDocs.ListId JOIN Webs ON Webs.Id=AllLists.tp_WebId ORDER BY webs.title

Output

19

Returns master pages in Web Application for all Web Sites

Query

SELECT AllDocs.Leafname AS FileName, AllDocs.Dirname AS “Folder Path”, AllLists.tp_Title AS “List Title”, Webs.Title AS “Web Title” FROM AllDocs JOIN AllLists ON AllLists.tp_Id=AllDocs.ListId JOIN Webs ON Webs.Id=AllLists.tp_WebId WHERE AllDocs.Extension = ‘master’ ORDER BY Webs.Title

Output

20

Returns Top 100 Documents that is versioned based on doc size

Query

SELECT TOP 100 Webs.FullUrl As SiteUrl, Webs.Title ‘Document/List Library Title’, DirName + ‘/’ + LeafName AS ‘Document Name’, COUNT(AllDocversions.UIVersion)AS ‘Total Version’, SUM(CAST((CAST(CAST(AllDocversions.Size as decimal(10,2))/1024 As decimal(10,2))/1024) AS Decimal(10,2)) ) AS ‘Total Document Size (MB)’, CAST((CAST(CAST(AVG(AllDocversions.Size) as decimal(10,2))/1024 As decimal(10,2))/1024) AS Decimal(10,2))   AS ‘Avg Document Size (MB)’ FROM Docs INNER JOIN AllDocversions ON Docs.Id = AllDocversions.Id INNER JOIN Webs On Docs.WebId = Webs.Id INNER JOIN Sites ON Webs.SiteId = SItes.Id WHERE Docs.Type <> 1 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’) GROUP BY Webs.FullUrl, Webs.Title, DirName + ‘/’ + LeafName ORDER BY ‘Total Version’ desc, ‘Total Document Size (MB)’ desc 

Output

21

Returns the Document List Name, File Name, URL, and the Content (Binary Format)

Query

SELECT AllLists.tp_Title AS “List Name”, AllDocs.LeafName AS “File Name”, AllDocs.DirName AS “URL”, DocStreams.Content AS “Document Contnt (Binary)” FROM AllDocs JOIN DocStreams ON AllDocs.Id=DocStreams.DocId JOIN AllLists ON AllLists.tp_id = AllDocs.ListId  

Output

22

Returns Documents By Age

Query

SELECT Webs.FullUrl AS SiteUrl, Webs.Title AS [Title], DirName + ‘/’ + LeafName AS [Document Name], Docs.TimeCreated 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 Docs.TimeCreated DESC

Output

23

Returns Total # of Documents

Query

SELECT COUNT(*) As “Total Number of Documents” 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’)

Output

24

Returns Total Size of All Content

Query

SELECT SUM(CAST((CAST(CAST(Size AS DECIMAL(10,2))/1024 AS DECIMAL(10,2))/1024) AS DECIMAL(10,2))) AS “Total 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 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’)  

Output

25

In the upcoming articles in this series I will try to add some useful queries that can retrieve some of the other pieces of vital information out of SharePoint.

Hope you find it helpful.

Executing Direct SQL Queries on SharePoint Content Databases: Is it a good idea???

This article is based on the findings and lessons learnt during one of my recent assignments which included the development of an Analysis Tool which can gather all Vitals out of a SharePoint Farm which can be further leveraged to take decisions during the migration at some later stage.

While deciding the direct execution of SQL Queries on SharePoint Databases, you should consider the following questions and plan accordingly-

What could be the possible repercussions if we execute direct SQL queries on Content Database?

  • Reading from the SharePoint databases programmatically, or manually, can cause unexpected locking within Microsoft SQL Server which can adversely affect performance.
  • Any read operations against the SharePoint databases that originate from queries, scripts, .dll files (and so on) that are not provided by the Microsoft SharePoint Development Team or by Microsoft SharePoint Support will be considered unsupported if they are identified as a barrier to the resolution of a Microsoft support engagement.
  • If unsupported read operations are identified as a barrier to the resolution of support engagement, the database will be considered to be in an unsupported state.
  • To return the database to a supported state, all unsupported read activities must stop.

What are unsupported operations on SharePoint Content Databases?

It is clearly unsupported to update, delete, or insert records. The risks are surely far more obvious. Also be aware that any database changes would definitely break the supportability as stated by Microsoft. Examples of such database changes include, but are not limited to the following:

  • Adding database triggers
  • Adding new indexes or changing existing indexes within tables
  • Adding, changing, or deleting any primary or foreign key relationships
  • Changing or deleting existing stored procedures
  • Calling existing stored procedures directly, except as described in the SharePoint Protocols documentation
  • Adding new stored procedures
  • Adding, changing, or deleting any data in any table of any of the databases for the products
  • Adding, changing, or deleting any columns in any table of any of the databases for the products
  • Making any modification to the database schema
  • Adding tables to any of the databases for the products
  • Changing the database collation
  • Running DBCC_CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (However, running DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD is supported, as these commands only update the indexes of the associated database.)
  • Enabling SQL Server change data capture (CDC)
  • Enabling SQL Server transactional replication
  • Enabling SQL Server merge replication

What are supported operations on SharePoint Databases?

  • Operations that are initiated from the SharePoint administrative user interface
  • SharePoint specific tools and utilities that are provided directly by Microsoft (for example, Ststadm.exe)
  • Changes that are made programmatically through the SharePoint Object Model and that are in compliance with the SharePoint SDK documentation

What happen if unsupported data modification is discovered?

If an unsupported database modification is discovered during a support call, the customer must perform one of the following procedures at a minimum:

  • Perform a database restoration from the last known good backup that did not include the database modifications
  • Roll back all the database modifications

What if previous version of the database that does not include the unsupported modifications is unavailable or if the customer cannot roll back the database modifications?

  • The customer must recover the data manually.
  • The database must be restored to an unmodified state before Microsoft SharePoint Support can provide any data migration assistance.
  • If it is determined that a database change is necessary, a support case should be opened to determine whether a product defect exists and should be addressed.

What can be done if still the content database needs to be queried directly for some reason?

Never run the direct SQL queries on Content Database in Production Environment

Take Following steps:

  • Restore the Database backup from Production to Development Environment
  • Take Database Offline
  • Run SQL Queries with [NOLOCK] option

Before running the above steps make sure the database is not in intermediate stage [nothing is checked out] else can get different document count then actual.

Key Takeaways: Based on the facts exposed by Microsoft Documentation on Direct Query Execution on Content Databases the key takeaways are:

  • This is completely unsupported by the EULA you agreed to when you installed SharePoint.
  • Your queries are not guaranteed to work after applying any patches or service packs to SharePoint since Microsoft could change the database schema anytime.
  • Directly querying the database can place extra load on a server and hence performance issues.
  • Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and hence stability issues.
  • Your custom queries might lead to incorrect data being retrieved.