In this article we will look for the PowerShell Scripts to export “SharePoint Content Databases” details from source SharePoint Farm. This information will be helpful to track all the “Content Databases” provisioned in SharePoint Farm.

In Step 1 we will add the PowerShell Snapin to PowerShell Script as usual

In Step 2 we define a function and initiate the export CSV file with Column Headers. For this demo I am exporting a few important properties like “Id, Content Database Name, Web Application Name, Server Name, Current Site Count” but you may query all possible properties as you deemed fit

In Step 3 we execute the “Get-SPContentDatabase” cmdlet to query the required properties

In Step 4 we loop through the properties collection for all Content Databases and list out the queried properties for each database

In Step 5 we add the content of properties for each of the Content Database to the CSV file

In Step 6 we will set the settings file path and call the function to export the Content Database Details

Once this script get executed successfully, it will export the Content Database Details in a CSV File as shown below in Step 7

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

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

Returns Root Site Title for each Site Collection available in WebApplication

Output

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

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

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

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

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

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

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.

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