SharePoint Component Inventory and Usage Using SQL Method Against Content Database Part One

Introduction

In this series of blogs, you will learn how to get the SharePoint customization usage using SQL Queries against the SharePoint Content Database instead of Object Model. This article will be useful for SharePoint On-Premise environment to gather the inventory and usage of customization.

This articles is part one of the series and will explain about Content Type, Types of Content Type and how we can get the inventory of content type usage across the SharePoint Farm.

Content Type: A content type is a reusable collection of site columns, workflow, and other settings for a group of items or documents associated to a list or document library and keep the information in a centralized way.

Types of Content Type

There are two types of Content Type available:

Site Content Type: Content type which is present at site level and can add this content type to any list/ library. Site Content type would be available to use in any sub-site, List or library.

List Content Type: Once you add a site content type into a list/library, it will make a local copy of site content type at list/library level and called List Content Type. List Content type is separate than Site Content Type. Changes made to the List Content type are only applicable to those lists where changes are made.

Note

List Content Type ID would be different than Site Content Type ID.
Changes made in List Content type could not be propagated to Parent Site Content Type level but any changes made at Site Content Type level can be pushed through all the list content type (child level).
Site Content Type can be present at root web level as well as in other sub-site level also.
Best Practice is always use site content type from the root web only and don’t break the inheritance and make the changes at sub-site level.

Way to Create Content Type

There are various ways to create content type:

Using Declarative XML: You can create content type using declarative XML in the element manifest file. In this scenario, Content Type would be stored at a File level in the SharePoint Hive and make an entry at database level also. Until and Unless you are not un-ghosting a content type, content type definition wouldn’t be push into the content database.

Here un-ghosting means push the element into Content Database level.

Using Code (Object Model): You can create content type by using Object Model code. You can use either SSOM (Server Side Object Model) or CSOM (Client Side Object Model) code.

Using User Interface: You can create site content type using User Interface also by browsing the site. In this scenario, content type definition would be pushed directly into the database level and no dependency would be present at file level.

Content Database Table Information

“ContentTypes” Table – This table is containing information about the Site Content Type.
“AllLists” Table -This table is containing information about the List Content Type.
“ContentTypeUsage” Table – This table is containing information about what are the places (List/Site) where content type is being used.

Important Column Details about the related tables

ContentTypes Table:

Class – This column will keep two values 0 and 1. If value equals to 1 it means corresponding entries is representing to a Content Type. i.e. Class=1 means Content Type.
Site ID – GUID of the site collection.
Scope – Scope will tell about at what scope content type is present. It is a simple text field which contains information in relative path format only. In case a content type is present at sub-web level then by using this column value only we can identify whether a content type is present at sub-web level also. Apart from this There is no column in the table which will tell about sub-web level information.
e.g.: “Sites/ABC” OR “Sites/ABC/XYZ”
Content Type ID - Column to identify a Content type by using guid value of content type.
Definition – This column will contain the information about content type definition. In some case definition, could be null, possible reason is Content type is not pushed at database level which means content type is still ghosted only.
ResourceDir – ResourceDIr is representing the internal folder name where content type definition is residing. In Most of the cases, this field value represents the content type name also.
SolutionID – This is a GUID value column which contain the information if a Solution is a Sandbox or Farm Solution. If this column is having any value which means it belongs to Sandbox Solution with the same ID else value is NULL which means this record is coming from FTC/OOTB solution.
IsFromFeature – This column will keep two values 0 and 1. This column will tell about if a content type still belongs to feature framework (file system) or not. 1 means it is coming from feature framework and 0 means it is not coming from feature framework.
DeleteTransactionID – This column will tell about if a content type is deleted or not. If column contains the ‘0x’ value it means it is not deleted else, it would be deleted.

AllList Table:

Tp_siteid – GUID value of a site.
Tp_webid -GUID value of a web where list/library is present.
Tp_ID – Unique ID of a list present in a site/web.
Tp_title – Title of the list.
Tp_ContentTypes – This column will provide the information about List Content Type. One challenge here is to get content type information in the data type of this column. This column is having compressed data type which keeps all the data in compressed format. So, we need to De-compress it to get the information. Which means there is no direct simple SQL Queries to get the information. You must iterate all the related records and decompress the value.

ContentTypeUsage Table:

SiteID – GUID value of the site
ContentTypeID – GUID value of the content type ID
ListID -GUID value of the List/Field
WebID – GUID value of the web
Class – contains two values 0 and 1. 1 means Content Type and 0 means Fields.
IsFieldID – if IsFieldID value is 1 means the value present in the ListID column present to a field not to a list. If value is 0 means value present to a list ID.

How to get the Usage of Site Content Type

Search all the records corresponding to a specific Content Type:

Select * from ContentTypes where Class=1
and ContentTypeId=0x00A7470EADF4194E2E9ED1031B61DA088402

Search all the records corresponding to a group of Content Type:

Select * from ContentTypes where Class=1
and ContentTypeId in (0x00A7470EADF4194E2E9ED1031B61DA088402, 0x00A7470EADF4194)

Search all the records corresponding to a specific Content Type and a specific site:

Select * from ContentTypes where Class=1
and ContentTypeId=0x00A7470EADF4194E2E9ED1031B61DA088402
and SiteId='A2BB2B98-4E88-4B13-960E-03FA9CD582CA'

Search all the records corresponding to a specific Content Type by Name:

Select * from ContentTypes where Class=1 and ResourceDir='Item'

Search corresponding to a specific sub site level:

Select * from ContentTypes where Class=1 and Scope='sites/ABC'
and ContentTypeId=0x00A7470EADF4194E2E9ED1031B61DA088402

How to get the Usage of List Content Type

Step 1: Get All the tp_contentTypes from AllList table

Select tp_ContentTypes from AllLists where tp_ContentTypes is not null

Step 2: Decompress the “tp_contentTypes” value using C# code

public static class CompressionUtility
{
public static string Decompress(byte[] compressedBytesBuffer)
{
if (compressedBytesBuffer[27] == 168 && compressedBytesBuffer[28] == 169)
{
using (DeflateStream str = new DeflateStream(new MemoryStream(compressedBytesBuffer, 41, compressedBytesBuffer.Length - 41), CompressionMode.Decompress))
{
StreamReader s = new StreamReader(str);
string xml = s.ReadToEnd();
return xml;
}
}
else
{
char[] chars = new char[(compressedBytesBuffer.Length - 28) / sizeof(char)];
Buffer.BlockCopy(compressedBytesBuffer, 27, chars, 0, compressedBytesBuffer.Length - 28);
return new string(chars);
}
}
public static byte[] ObjectToByteArray(Object obj)
{
byte[] byteArray = null;
if (obj == null)
return null;
BinaryFormatter bf = new BinaryFormatter();
using (MemoryStream ms = new MemoryStream())
{
bf.Serialize(ms, obj);
byteArray = ms.ToArray();
}
return byteArray;
}
}

Here is the method to call the above mentioned utility functions to get the value:

string ContentTypeXml = string.Empty;
ContentTypeXml = CompressionUtility.Decompress(CompressionUtility.ObjectToByteArray(drow["tp_ContentTypes"]));

Note: We can use Content Type Usage table also to get the usage of content type but I saw sometimes data is not present in the table in some scenarios so I'm not referring to it here. If anybody wants to, they can use and make the query and get the result.

In my next blog, I will provide information about Event Receiver Footprint at content database level.