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
  1. “ContentTypes” Table – This table is containing information about the Site Content Type.
  2. “AllLists” Table -This table is containing information about the List Content Type.
  3. “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:
AllList Table:

ContentTypeUsage Table:

How to get the Usage of Site Content Type

Search all the records corresponding to a specific Content Type:

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

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

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

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

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

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

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

Search corresponding to a specific sub site level:

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

How to get the Usage of List Content Type

Step 1: Get All the tp_contentTypes from AllList table

  1. 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. using (DeflateStream str = new DeflateStream(new MemoryStream(compressedBytesBuffer, 41, compressedBytesBuffer.Length - 41), CompressionMode.Decompress))
  8. {
  9. StreamReader s = new StreamReader(str);
  10. string xml = s.ReadToEnd();
  11. return xml;
  12. }
  13. }
  14. else
  15. {
  16. char[] chars = new char[(compressedBytesBuffer.Length - 28) / sizeof(char)];
  17. Buffer.BlockCopy(compressedBytesBuffer, 27, chars, 0, compressedBytesBuffer.Length - 28);
  18. return new string(chars);
  19. }
  20. }
  21. public static byte[] ObjectToByteArray(Object obj)
  22. {
  23. byte[] byteArray = null;
  24. if (obj == null)
  25. return null;
  26. BinaryFormatter bf = new BinaryFormatter();
  27. using (MemoryStream ms = new MemoryStream())
  28. {
  29. bf.Serialize(ms, obj);
  30. byteArray = ms.ToArray();
  31. }
  32. return byteArray;
  33. }
  34. }

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

  1. string ContentTypeXml = string.Empty;
  3. 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.