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,

Note

Way to Create Content Type

There are various ways to create content type,

Content Database Table Information

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

Important Column Details about the related tables

ContentTypes Table

AllList Table

ContentTypeUsage Table

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,

  1. public static class CompressionUtility
  2. {
  3. public static string Decompress(byte[] compressedBytesBuffer)
  4. {
  5. if (compressedBytesBuffer[27] == 168 && compressedBytesBuffer[28] == 169)
  6. {
  7. // The data was compressed (as of SharePoint 2010). So we can decompress
  8. using (DeflateStream str = new DeflateStream(new MemoryStream(compressedBytesBuffer, 41, compressedBytesBuffer.Length - 41), CompressionMode.Decompress))
  9. {
  10. StreamReader s = new StreamReader(str);
  11. string xml = s.ReadToEnd();
  12. return xml;
  13. }
  14. }
  15. else
  16. {
  17. // The data was stored without compression, convert bytes to a string
  18. char[] chars = new char[(compressedBytesBuffer.Length - 28) / sizeof(char)];
  19. Buffer.BlockCopy(compressedBytesBuffer, 27, chars, 0, compressedBytesBuffer.Length - 28);
  20. return new string(chars);
  21. }
  22. }
  24. public static byte[] ObjectToByteArray(Object obj)
  25. {
  26. byte[] byteArray = null;
  27. if (obj == null)
  28. return null;
  30. BinaryFormatter bf = new BinaryFormatter();
  31. using (MemoryStream ms = new MemoryStream())
  32. {
  33. bf.Serialize(ms, obj);
  34. byteArray = ms.ToArray();
  35. }
  37. return byteArray;
  38. }
  39. }
Here is the method to call the above mentioned utility functions in order to get the value.
  1. string ContentTypeXml = string.Empty;
  2. 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.