SharePoint Customization

Introduction

In this series of articles, you will learn how to get the SharePoint customization used, 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
The content type is present at site level and we 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 a site content type is added into a list/library, it will make a local copy of site content type at list/library level . That is called List Content Type. List content type is separate from 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 from Site Content Type ID.
Changes made in the List Content type could not be propagate 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

Following are the table present at Content Database level where you can find the content type detail,

“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 if a content type present at sub-web level then by using this column value only we can identify if 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, a possible reason is Content type is not pushed at database level 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 contains the information if a Solution is a Sandbox or Farm Solution. If this column is having any value it means it belongs to Sandbox Solution with the same ID else if value is NULL it means this records 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 is here to get content type information is the data type of this column. This column is having compressed data type which keep all the data in compressed format. So, 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)
{
// The data was compressed (as of SharePoint 2010). So we can decompress
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
{
// The data was stored without compression, convert bytes to a string
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 in order 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 that sometimes, the data is not present in the table in some scenarios. If anybody wants, they can use and make the query and get the result.

In my next article, I will provide information about Event Receiver Footprint at Content Database level.